How to remove duplicate results?

Question

I'm coding a message system using mysql. Everything works fine when I list users whom I'm conversing with, until I want to add date of the last or the start of conversation. When I add a.date I get duplicate results when the date isnt the same.

Here is my sqlfiddle

You want the latest date per user ? If yes, then simply add `GROUP BY` and get max of the date for a user. See: http://sqlfiddle.com/#!9/5d0482/8 — Madhur Bhaiya, Oct 07 '19 at 11:29
@MadhurBhaiya is there a way to also get the txt for the same row as the date ? — daviddev38, Oct 07 '19 at 11:45
To get txt also, please check the answers in the duplicate marked question. This is a very common problem. I have added [tag:greatest-n-per-group] tag to the question. You can get a plethora of techniques here: https://stackoverflow.com/questions/tagged/greatest-n-per-group?tab=Votes — Madhur Bhaiya, Oct 07 '19 at 11:53

score 1 · Accepted Answer · answered Oct 07 '19 at 11:39

Since, you were pulling only user_id then in both cases (send/recieve) it was giving you distinct record. But now with date it is no more distinct. you need to do something like:

SELECT temp.id_user, MAX(temp.date) as date
FROM 
(
SELECT users.id_user,
       a.date
FROM   users
       LEFT JOIN message AS a
              ON users.id_user = a.id_user_recipient
       LEFT JOIN message AS b
              ON a.id_user_recipient = b.id_user_sender
WHERE  a.id_user_sender = 1
UNION DISTINCT
SELECT users.id_user,
       a.date
FROM   users
       LEFT JOIN message AS a
              ON users.id_user = a.id_user_sender
       LEFT JOIN message AS b
              ON a.id_user_sender = b.id_user_recipient
WHERE  a.id_user_recipient = 1  
) as temp 
GROUP BY temp.id_user;

Grabbing max(date) will ensure to return only one record as with group by

this worked great, but if i want to show the 'a.txt' it show the old txt not the latest. — daviddev38, Oct 07 '19 at 11:50

How to remove duplicate results?

1 Answers1