CODEWITHSUNDEEP
phpmysqlsql
user2250471
user2250471

Reputation: 1112

select distinct column and then related columns in php/sql

I'm trying to create an Inbox list, where only messages with distinct "parent ids" are displayed. So the parent id for a message from user1 to user2 will be the same as the parent id of a message from user2 to user1. Thus, my inbox will only display one item for every unique conversation. But I can't figure out how to select the distinct parent id and THEN the related message information. This is what I have now:

$sql = "SELECT sender, receiver, message, timestamp, (distinct parent)
    FROM messages WHERE receiver='$log_username' OR sender='$log_username' 
    ORDER BY timestamp DESC
    ";

I want to be able to grab the most recent message row from every unique parent group, and then put the parent groups with the most recent messages at the top of the inbox list. I can select distinct parent groups fine, but then I can't figure out how to also get the sender/receiver/message/timestamp data of the most recent message in that parent group.

Upvotes: 1

Views: 243

Answers (1)

Tom
Tom

Reputation: 6663

This should give you what you are looking for. It will give you one message per parent and order by the most recently received message.

SELECT sender, receiver, message, timestamp, parent

FROM messages 

WHERE receiver='$log_username' OR sender='$log_username' 

GROUP BY parent

ORDER BY timestamp DESC

Upvotes: 1

Related Questions