CODEWITHSUNDEEP
phpmysqlsql
user2250471
user2250471

Reputation: 1112

order rows within group by timestamp

I am trying to build and inbox for private messages.

$sqlInbox = "
    SELECT sender, receiver, message, parent, rView
    FROM messages 
    WHERE receiver='$log_username' OR sender='$log_username' 
    GROUP BY parent 
    ORDER BY timestamp DESC";

This code orders each group by timestamp, so if user1 sends me a message after user2, user1 will be first on the list. However, the problem I am having is that the messages within each group are NOT being ordered by timestamp, so the output for a group is always the first message that user sent, when I need it to be the most recent message that user sent. So, I need to order the rows within the group by timestamp, as well as ordering the groups as a whole by timestamp. I cannot for the life of me figure out how to do this. Can someone help? (parent is a common ID for all messages that share two common users regardless of sender/receiver, i.e., the collection of all messages between any two users). Thanks in advance!

Upvotes: 0

Views: 71

Answers (1)

Fluffeh
Fluffeh

Reputation: 33542

I need coffee, so I hope I am reading your question correctly, but isn't it just a matter of adding the parent to the order by statement?

$sqlInbox = "
    SELECT sender, receiver, message, parent, rView
    FROM messages 
    WHERE receiver='$log_username' OR sender='$log_username' 
    GROUP BY parent 
    ORDER BY parent, timestamp DESC";

And on that note, you aren't using any aggregate functions, so you probably don't even need the group by (unless you are doing something else not in the code you posted:

$sqlInbox = "
    SELECT sender, receiver, message, parent, rView
    FROM messages 
    WHERE receiver='$log_username' OR sender='$log_username' 
    ORDER BY parent, timestamp DESC";

Lastly, if it were my own code, I would consider adding a read/unread flag to the messages and using that additionally in the order by clause:

$sqlInbox = "
    SELECT sender, receiver, message, parent, rView
    FROM messages 
    WHERE receiver='$log_username' OR sender='$log_username' 
    ORDER BY readStatus, parent, timestamp DESC";

This would split the whole thing into two sections - unread messages at the top, and read messages at the bottom - with the same other ordering as before.

Upvotes: 1

Related Questions