MySQL show the row with the latest Date for each different value in other column?

Question

I'm working with a mysql query that is supposed to select all messages addressed or sent by the user. I need to group all messages with same UID so that I show a single thread for each differente user (this means it should eliminate all messages except the last with same UID). My problem is that I started using GROUP BY to do it but sometimes the row that remains is actually the older message instead of the latest.

This is what I was trying:

SELECT `UID`, `Name`, `Text`, `A`.`Date`
FROM `Users`
INNER JOIN (
    (
    SELECT *, To_UID AS UID FROM `Messages` WHERE `From_UID` = '$userID' AND `To_UID` != '$userID'
    )
    UNION ALL
    (
    SELECT *, From_UID AS UID FROM `Messages` WHERE `To_UID` = '$userID' AND `From_UID` != '$userID'
    )
) AS A
ON A.UID = Users.ID
    GROUP BY UID // This doesn't work

How can I show only the row with the most resent date per UID?

score 1 · Accepted Answer · edited Aug 18 '12 at 04:29

1

use DISTINCT and only use ORDER BY date
GROUP BY actually sometimes displays a random row, which isn't always commonly discussed.

edited Aug 18 '12 at 04:29

Himanshu

31,810
31
111
133

answered Aug 18 '12 at 03:22

Jared Drake

982
4
12

awesome, I had already used a complicated self join I'll change it when I have the time – lisovaccaro Aug 18 '12 at 04:30

score 0 · Answer 2 · answered Aug 18 '12 at 04:09

you can try some thing like this:

select UID, Name, Text, c.date 
from User
inner join (
    select if(b.From_UID = '$userID', b.To_UID, b.From_UID) as UID, 
           * 
           from Messages as b 
           inner join(
               select if(c.From_UID = '$userID', c.To_UID, c.From_UID) as UID, 
                     max(c.date) as date 
                     from Messages as c
                     where c.From_UID = '$userID' or c.To_UID = '$userID' 
                     group by UID
            ) as d on d.date = b.date and d.UID = b.UID
     ) as e on e.UID = Users.id
)

or create a temp table / stored procedure to make life easier

Temp table

create temp table t 
    select if(From_UID = '$userID', To_UID, From_UID) as UID, * from Messages


select UID, Name, Text, date
from User
inner join (
    select * 
        from t as t1
        inner join( 
            select 
                t2.UID, 
                max(t2.date) as date
                from t as t2
                group by t2.UID
         ) as t3 on t3.date =  t1.date and t3.UID = t1.UID
    ) as e on e.UID = Users.id

i didn't test the code, but the concept from http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column should work for this greatest-n-per-group — lucemia, Aug 18 '12 at 04:10

MySQL show the row with the latest Date for each different value in other column?

2 Answers2