PHP MYSQL Ordering by ID , then grouping results by a column

[kdjernigan]

So..here is the thing, In my database, I've got this...

message_id | group_id | from_user | to_user | message_read
1 123 TW KJ 1
2 245 TW MI 0
3 123 KJ TW 0



This is my current query code :

$get_messages2 = mysql_query("SELECT * FROM messages WHERE to_user='$userfinal' GROUP BY group_id ORDER BY message_id ASC") or die(mysql_error());

This is grouping the messages together to show all messages between two people together as one message. It only shows the username of the other person in the inbox (this works).

I also have another query running to display whether there is a new message for each category (this works)


What doesnt work
The inbox does not show the groups with "NEW" messages first. If someone reply's to someones message, it displays NEW next to the group; however, it does not bring the group to the top of the list. I cannot figure out why. I have order by message_id which is auto increment, so I really can't figure out why its not bringing it up to the top.

Note: I am NOT trying to make it to where the page automatically updates. I'm talking about wqhen the person comes back tgo their inbox, the category stays where its at in the list, even with a new message in it.

Any ideas?

All I want is for the group_id's with the LATEST message_id to display first.

[rocknbil]

ASC = lowest to highest value.
DESC = highest to lowest value.

Change asc to desc.

[kdjernigan]

The problem is no sorting is working, no matter what I do. I'm not sure what to do at this point, but I need e newest message_id at the top and I need it to remain grouped.

[rocknbil]

Did you **try** removing the group by clause? It doesn't do what most coders think . . .

select * from messages where to_user='$userfinal' order by group_id desc, message_id desc

[kdjernigan]

I would love to; however, I actually need to use group unless there is another way to do what I want. Here's what I want: I want alist of people you currently have conversations started with in the messaging system. The messages are combined together using the same group_id. I do NOT want the same person listed multiple times, so I only want each group_id to appear once in the list. I am displaying by username, but that is the easy part. The problem I'm having is getting the groups and having the latest message_id (AI) first. This should be possible some way. It's pretty much same as facebook, how it moves the persons name to the top of your messages when they send you a message.