Select distinct values based on a column with maximum date value from second column

Question

The title is a bit complex. I apologize but the query is also complex for a non-SQL person.

I have a table messages that has the following structure:

Message(id, sender_id, receiver_id, message_datetime)

My goal is to select the last messages received by a receiver_id from distinct senders.

When I do for example:

SELECT * 
FROM  `message` 
WHERE  `receiver_id` =1

I get something like:

1005  |   2    |    1    |  2015-11-08
1004  |   3    |    1    |  2015-11-07
1003  |   3    |    1    |  2015-11-06
1002  |   2    |    1    |  2015-11-05 
1001  |   2    |    1    |  2015-11-04

While I need something like:

1005  |   2    |    1    |  2015-11-08
1004  |   3    |    1    |  2015-11-07

Your usual expert guidance is highly appreciated. I am really stuck with such a query. Thanks again and sorry for the bad formatting.

score 2 · Accepted Answer · edited Nov 27 '15 at 12:53

2

You need to create a subquery that returns the maximum message id by sender_id for a given receiver and join it to the messages table to get all other fields:

SELECT m.* 
FROM  `message` AS m
INNER JOIN (SELECT sender_id, MAX(message_date) as md
                FROM message WHERE  `receiver_id` =1 GROUP BY sender_id) AS t
ON m.message_date=t.md and m.sender_id=t.sender_id
WHERE  `receiver_id` =1

edited Nov 27 '15 at 12:53

Adib Aroui

4,981
5
42
94

answered Nov 25 '15 at 13:28

Shadow

33,525
10
51
64

Thank you for your time sir, I am reading carefully your query and comparing it with Undefined_Variable one (which already works). I will be learning from it of course. +1 – Adib Aroui Nov 25 '15 at 13:30
Pls elaborate on what error message or unexpected behaviour you experience. – Shadow Nov 26 '15 at 17:26
For the sake of sharing, I advise to read this:http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column/7745635#7745635 since the approach there helps convert the same query from raw SQL to an object query language such as Doctrine Query Builder. – Adib Aroui Nov 27 '15 at 02:48
So as I mentionned in my last comment, a more efficient way (without using aggreagate functions and that is compatible with DQL and doctrine query builder) is: `SELECT a.* FROM message a LEFT OUTER JOIN message b ON a.sender_id=b.sender_id and a.message_date < b.message_date WHERE b.id is null and a.receiver_id=372`. Just to share this new trick for people who maybe will need it, but Shadow answer remains correct. – Adib Aroui Nov 27 '15 at 12:12
I faced a lot of problems when I tried to convert this query to an object query language (like DQL or Doctrine querybuildere) so I found a new one without joins `select m.* from message m where m.message_date in (select max(t.message_date) from message t where m.sender_id=t.sender_id and t.receiver_id=1 group by t.sender_id) and m.receiver_id=1`. What do you think about it regarding standards? – Adib Aroui Dec 02 '15 at 21:41
Instead of a join, it uses in() with a subquery, which does not scale well, but may be faster on smaller tables. What's your problem with joins? – Shadow Dec 02 '15 at 21:52
My tables are intended to be big in the future (I hope :)). About the issue, Doctrine queryBuilder has a bunch of functions dealing with joins but reading the doc and using SO answers didn't help me create mine. I always get errors. This is also the case with Doctrine Query Language. I succcessfuly create any type of query except ones that contains joins. I never succeded and start to have headackes today.I gave up I will stick to `IN` for the moment since it works with DQL. Thank you very much for confirming to me that the query is ok except for scalabilty. – Adib Aroui Dec 02 '15 at 22:08
Create a separate question then how to create the above query with join in dql. – Shadow Dec 02 '15 at 22:40
I already did to no avail (linked question in right sidebar with 0 votes), I will retry tomorrow differently it is midnight here. Thank you very much sir – Adib Aroui Dec 02 '15 at 22:57

score 0 · Answer 2 · answered Nov 25 '15 at 13:25

0

    Select * from (
           Select * from `Message` where receiver_id=1 order by message_datetime desc
) a group by a.sender_id

This query first orders the data by date and then it group by sender_id.

answered Nov 25 '15 at 13:25

undefined_variable

6,180
2
22
37

Thank you Sir for the query, I will give it a try in a while and come to feedback (although I feel it is already the right direction towards solution) – Adib Aroui Nov 25 '15 at 13:27
Perfect, waiting for timer to expire. Thanks a lot +1 – Adib Aroui Nov 25 '15 at 13:28
The only problem with this solution is that it depends on certain mysql config settings to run and the code will not be portable. If you want to take advantage of mysql not necessarily requiring all fields to be in the group by crlause or subject to a summary function, then you do not even need to use a subquery. – Shadow Nov 25 '15 at 13:30
Given that there are other, better, documented solutions, I think this undocumented hack is best consigned to oblivion – Strawberry Nov 25 '15 at 13:33
2

select max(id) as id, sender_id, receiver_id, max(message_datetime) as message_datetime from message group by sender_id where receiver_id=1 – Shadow Nov 25 '15 at 13:33
I gave a time to the three queries and I really respect the last one in the comment (it just needs WHERE clause before GROUP BY clause) since it uses no subqurey and is short. About underfined_variable answer being a hack, I don't understand why?! Thanks averybody for this amount of learning – Adib Aroui Nov 25 '15 at 14:23
It does not comply with the SQL standard. In mysql you can configure how strictly to comply with the SQL standard using sql_mode variable. Depending on the settings, the query in this answer will either run or not run. – Shadow Nov 25 '15 at 14:43

Select distinct values based on a column with maximum date value from second column

2 Answers2

Linked