Eliminate fields with distinct

Question

i use distinct to eliminate duplicate fields.

Query:

SELECT DISTINCT
  userId1, userId2, message, sentDate, ad1, avatar1, ad2, avatar2 
FROM 
(SELECT m.id, m.userId1, m.userId2, m.message, m.sentDate, m.readDate,
   u1.id id1, u1.ad ad1, u1.avatar avatar1, u2.id id2, u2.ad ad2, 
   u2.avatar avatar2 
 FROM (messages m inner join users u1 on m.userId1 = u1.id) 
 inner join users u2 on m.userId2 = u2.id
 where (userId1 = 8 or userId2 = 8) 
 order by sentDate desc
) as q

response

if one field equal to userId1 = 7 and userId2 = 8 don't take userId1 = 8 and userId2 = 7. How can i say this to sql?

What do you do with the other row you want to "eliminate"? Simply ignore it? Which one to ignore: 7-8 or 8-7? — The Impaler, Oct 30 '18 at 21:33
@TheImpaler yes simply ignore, thats the logic: order the fields by sentDate and which one is old than other, ignore it. — Kaan, Oct 30 '18 at 21:44

Barmar · Answer 1 · 2018-10-31T16:50:12.373

0

Use one of the techniques in SQL select only rows with max value on a column to get the row with the highest sentDate in a group. Use

GROUP BY GREATEST(userid1, userid2), LEAST(userid1, userid2)

as the grouping.

edited Oct 31 '18 at 16:50

answered Oct 30 '18 at 21:58

Barmar

741,623
53
500
612

this is a good idea but i can't change the fields value, otherwise data'll get wrong. – Kaan Oct 30 '18 at 23:23
The two rows have different values in the other column. How do you decide which row to keep? – Barmar Oct 31 '18 at 14:06
Order the fields by sentDate and which one is new than other, keep it. – Kaan Oct 31 '18 at 16:45

Eliminate fields with distinct

1 Answers1