Search code examples
mysqlsqlgreatest-n-per-group

Group by and get latest record in group


i have a table named messages like this : enter image description here

I want query for where reciever_id equal 1 and group by sender_id and get latest record.

I USED QUERY :

SELECT `t`.* FROM(
    SELECT * FROM messages
    WHERE reciever_id = 1
    ORDER BY created_at DESC
) `t`
GROUP BY `sender_id`
ORDER BY `id`

enter image description here

AND ALSO :

SELECT message, MAX(created_at) 
FROM messages
WHERE reciever_id = 1
GROUP BY sender_id
ORDER BY created_at

enter image description here

Date's column created_at in picture exactly are the latest and id's also ordered and are latest also.


Solution

  • I'm done this after releasing question by below query, but i think this can cost more than others... there are another way to do this with low cost?!

    Can somebody say formula how times multiple joins below query has cost?

    SELECT id,sender_id,reciever_id,seen,message,created_at
    FROM messages
    WHERE id IN (
        SELECT MAX(id)
        FROM messages
        WHERE reciever_id = 1
        GROUP BY sender_id
        ORDER BY id desc
    ) ORDER BY created_at DESC