ORDER BY then GROUP BY

[chrisprse]

Hello

 

I am building a text message (SMS) application that has "incoming messages" and "sent messages" merged into one conversation. The list of conversations is ordered by the most recent activity with that telephone number.

 

Basically, I have these fields in the "inbox" table:

 

id

client_id

from

message

timestamp

 

And these fields in the "sent_items" table:

 

id

client_id

to

message

timestamp

 

If I had to write a MySQL query based on my thoughts, this is how it would look:

 

JOIN inbox AND sent_items ORDER BY `timestamp` DESC GROUP BY inbox.from = sent_items.to WHERE `client_id` = ? LIMIT 0, 20;

 

So basically, I want to join two basic SELECT queries (UNION)?

 

Then I want to order the whole lot by the shared `timestamp` field in DESC order.

 

Then I want to GROUP BY the inbox.from column and sent_items.to column which both contain telephone numbers.

 

This query should leave me with the latest 20 results (conversations) (20 different phone numbers) and I want to be able to access the timestamp and message in the result set.

 

Does this make sense?

 

I've spent a whole day on this, looking at LEFT JOINS, UNIONS, etc and I can't figure out a way!

 

Any help would be much appreciated.

 

Regards

Chris

[cags]

How strict is your table structure? What I mean is do you have to keep that table structure or are you open to suggestions?