abc abc - 6 months ago 27
MySQL Question

How to get first row from each group from a table in sqlitedatabase?

I have a table messages in my sqlite database.This table has three columns msg_id,sender_id,msg_body. I need to get one latest message with each user_id and these messages should be ordered in desc order.
for eg. my messages table is

msg_id | sender_id | msg_body
--------+-----------+----------
1 | 18 |"something"
2 | 18 |"something"
3 | 19 |"something"
4 | 19 |"something"


What I want is table:-

msg_id | sender_id | msg_body
-------+-----------+----------
4 | 19 |"something"
2 | 18 |"something"

Answer

Try this:

SELECT t1.*
FROM mytable AS t1
JOIN (
   SELECT sender_id, MAX(msg_id) AS max_msg_id
   FROM mytable
   GROUP BY sender_id
) AS t2 ON t1.sender_id = t2.sender_id AND t1.msg_id = t2.max_msg_id
Comments