CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
lisovaccaro
lisovaccaro

Reputation: 33996

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

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?

Upvotes: 0

Views: 742

Answers (2)

Jared Drake
Jared Drake

Reputation: 1002

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

Upvotes: 1

lucemia
lucemia

Reputation: 6627

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

Upvotes: 0

Related Questions