PHP MySQL独有的列，但使用最新的行

Question

I'm having a lot of trouble applying other people's answers to this question, so I decided to just post it here to see if any of you could help me. I am trying to get a unique list of people messaged from a database where the fields are:

id, sender, recipient, timestamp, message

The problem is I would like to display the unique list with the most recent messaged user first, but MySQL seems to in the process of making it unique choose the rows added earliest. Can you help me reverse that? This is the query I am currently using.

SELECT DISTINCT recipient, sender FROM chat WHERE recipient = '$username' OR sender = '$username' ORDER BY timestamp DESC limit 50

Thank you so much!

Answer 1

Sadikhasan is on the right way, but i think you need to ORDER BY before you GROUP BY. Also, I undertand it as you wish to get a list of unique conversations (unique combinations of sender AND recipient) where $username is one of the partakers. Then you'll have to do this:

SELECT * FROM (
SELECT recipient,
       sender
FROM chat
WHERE
recipient = '$username' OR
sender = '$username'
ORDER BY timestamp DESC LIMIT 50) AS tempTable
GROUP BY recipient, sender