[SOLVED] GROUP BY get latest record in each GROUP

[AE117]

Here is what I am trying to do I have an inbox that has a user_sender_id column. This is the column that is being grouped by. SO if you have 5 messages from the same user they all don't so. You just see that you have received a message. So you have multiplie users sending you info. What I need is to arragne this data so the User that sent you the newest message is on top.

 

I have tried this

 

 "SELECT * FROM messages WHERE (receiver_user_id=".escape($user_id).") AND (deleted_by_receiver = 0 OR deleted_by_sender = 0) GROUP BY sender_user_id ORDER BY stro ASC";

 

However since it is grouped the message that is being ORDER BY is the first so the lowest value - ---

 

I have read many things and Came to this

 

"SELECT s1.sender_user_id, message_id, receiver_user_id, timestamp, message, status, deleted_by_receiver, deleted_by_sender, s1.stro 
FROM messages s1
(SELECT sender_user_id, MAX(stro) AS stro FROM messages GROUP BY sender_user_id) 
AS s2 ON s1.sender_user_id = s2.sender_user_id AND s1.stro = s2.stro";

 

But this does not work either. I need to display the lastest record From a Group BY

 

Can anyone help?? THANKS

[kickstart]

Hi

 

Your 2nd try looks to be almost there. You just need an order by clause at the end (and you don't need the AS in "AS s2").

 

In your first try group by will make a mess. To be useful any field returned needs to be an aggregate field or a field in the group by clause. Otherwise which rows data that is returned will be largely random (and most flavours of SQL would just fail).

 

All the best

 

Keith

[AE117]

Seems to be working Thank you