CODEWITHSUNDEEP
mysqlgroup-bydistinct
vasanth.v
vasanth.v

Reputation: 213

Mysql using DISTINCT on 2 fields only

I have a mysql database table as follows

id | uid | touid | message | time

This database has the messages sent from one person to another person. I need to fetch the latest messages transferred between me and all other users.

Currently I am using a query as follows:

SELECT uid, touid, message, time
FROM messages
WHERE uid = "'.$currentuser.'"
    OR touid = "'.$currentuser.'"
ORDER by time DESC;

Which will get all the message between me and any other user. But I need to show only the set latest messages transferred between me and all other users.

It tried using GROUP BY but the ordering gets affected. So is there any other solution?

Any ideas? If I am not clear please comment

Upvotes: 0

Views: 162

Answers (4)

Jonathan Hall
Jonathan Hall

Reputation: 79674

SELECT uid,touid,message,time
FROM messages AS m
JOIN (
    SELECT uid,touid,MAX(time) AS time
    FROM messages
    WHERE uid = "'.$currentuser.'"
        OR touid = "'.$currentuser.'"
) AS x ON (m.uid=x.uid,m.touid=x.touid,m.time=x.time)
ORDER BY time DESC;

Upvotes: 0

CristiC
CristiC

Reputation: 22698

SELECT 
  m1.*
FROM 
  messages m1
 JOIN 
  (SELECT UID, TOUID, MAX(time) time FROM messages GROUP BY UID, TOUID) m2
 ON m1.UID=m2.UID AND m1.TOUID=m2.TOUID and m1.time=m2.time
WHERE 
  m1.uid = "'.$currentuser.'"
ORDER BY
  m1.time DESC;

Upvotes: 1

triclosan
triclosan

Reputation: 5724

try to use to cut unwanted rows

limit 1

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115590

Add LIMIT 1 in the end of your query.

Upvotes: 0

Related Questions