i have a table named messages like this :
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`
AND ALSO :
SELECT message, MAX(created_at)
FROM messages
WHERE reciever_id = 1
GROUP BY sender_id
ORDER BY created_at
Date's column created_at in picture exactly are the latest and id's also ordered and are latest also.
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