select distinct column and then related columns in php/sql

Question

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.

Tom · Accepted Answer · 2013-10-24 18:12:06Z

1

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

answered Oct 24, 2013 at 18:12

Tom

6,6633 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select distinct column and then related columns in php/sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related