CODEWITHSUNDEEP
mysqlsql
Paul
Paul

Reputation: 11746

Getting the most recent date after using group by with mySQL

I have a table that looks like the sample below. I need to retrieve the most recent date for a specified user. 

id sender_id receiver_id content date               
-- --------- ----------- ------- ----               
1  5         7           test    2013-03-13 10:33:54
2  13        7           test 2  2012-03-13 12:01:36
3  5         7           test 3  2013-01-05 09:15:37
4  13        7           test 4  2013-04-05 08:17:39

My current SQL looks like this:

SELECT * FROM `messages` WHERE receiver_id=7 group by sender_id

I would expect the result set to include just two entries with the most recent date for each like this:

4  13        7           test 4  2013-04-05 08:17:39
1  5         7           test    2013-03-13 10:33:54

Do I need to do some sort of sub select to first get the most recent date?

Upvotes: 1

Views: 4283

Answers (1)

fthiella
fthiella

Reputation: 49049

You are grouping by sender_id but returning all columns. The value of non-aggregated rows could be underemined and not affected by an order by.

You could use a query like this to get the results that you need:

SELECT *
FROM   messages
WHERE  (sender_id, receiver_id, date) IN (
          SELECT   sender_id, receiver_id, MAX(date)
          FROM     messages
          WHERE    receiver_id=7
          GROUP BY sender_id, receiver_id)

Please see fiddle here.

Upvotes: 3

Related Questions