How do I query messages stored in a table such that I get messages grouped by sender and the groups sorted by time?

Question

Overall Scenario: I am storing conversations in a table, I need to retrieve the messages for a particular location, such that they're grouped into conversations, and the groups of conversations are sorted by the most recent message received in that group. This is analogous to how text messages are organized on a phone or facebook's newsfeed ordering. I'm storing the messages in the following schema:

Location_id | SentByUser | Customer     | Messsage         | Time                 
1           | Yes        | 555-123-1234 | Hello world      | 2013-12-01 10:00:00
1           | No         | 555-123-1234 | Thank you        | 2013-12-01 12:00:00 
1           | Yes        | 999-999-9999 | Winter is coming | 2013-12-03 11:00:20 
1           | Yes        | 555-123-1234 | Foo Bar          | 2013-12-02 11:00:00 
1           | No         | 999-999-9999 | Thank you        | 2013-12-04 13:00:00 
1           | Yes        | 111-111-1111 | Foo Foo Bar      | 2013-12-05 01:00:00

In this case, if I was building the conversation tree for location id, I'd want the following output:

Location_id | SentByUser | Customer     | Messsage         | Time                 
1           | Yes        | 111-111-1111 | Foo Foo Bar      | 2013-12-05 01:00:00
1           | Yes        | 999-999-9999 | Winter is coming | 2013-12-03 11:00:20 
1           | No         | 999-999-9999 | Thank you        | 2013-12-04 13:00:00 
1           | Yes        | 555-123-1234 | Hello world      | 2013-12-01 10:00:00
1           | No         | 555-123-1234 | Thank you        | 2013-12-01 12:00:00 
1           | Yes        | 555-123-1234 | Foo Bar          | 2013-12-02 11:00:00 

So what I'd like to do is group all the conversations by the Customer field, and then order the groups by Time, and lastly order the messages within each group also. This is because I'm building out an interface that's similar to text messages. For each location there may be hundreds of conversations, and I'm only going to show a handful at a time. If I ensure that my query output is ordered, I dont have to worry about server maintaining any state. The client can simply say give me the next 100 messages etc.

My question is two fold: 1. Is there a simple way to sub order results? Is there an easy way without doing a complex join back on the table itself or creating a new table to maintain some order. 2. Is the way I'm approaching this a good practice? As in, is there a better way to store and retrieve messages such that the server doesn't have to maintain state? As in, is there a better pattern that I should consider?

I looked at various questions and answers, and the best one I could find was What is the most efficient/elegant way to parse a flat table into a tree?, but it doesnt seem fully applicable to my case because the author is talking about multi branch trees.

Answer 1

It seems like you want two different queries. This is written in T-SQL for SQL Server, but could easily be adapted for SQLite or MySQL or whatever you're working with.

1) Show me the Customer groups ordered by most recent

select Location_id, Customer, Max(Time) as LatestMessageTime from #Table 
group by Location_id, Customer order by LatestMessageTime desc

This would be similar to the first view of your text message application.

2) Show me the Messages in order given a Location_id and Customer

declare @Location int, @Customer varchar(900)
set @Location = 1
set @Customer = '999-999-9999'

select * from #Table where Location_id = @Location and Customer = @Customer 
order by Time desc

If you just wanted the sample output, you don't need anything too complex:

select t.*, g.MostRecentTime from #Table t LEFT OUTER JOIN
(select Location_id, Customer, Max(Time) as MostRecentTime from #Table
group by Location_id, Customer) g on g.Location_id = t.Location_id and g.Customer = t.Customer
order by MostRecentTime desc, Location_id, Customer, Time

Here's a SQLFiddle of it: http://sqlfiddle.com/#!6/ae3f8/1/0

I think this is an acceptable way to store the information. As far as retrieving it I'd have two different stored procedures: Give me the 'summary' (1 above), and then give me the 'messages' given a certain location and customer (2 above). I'd also order by .... Customer, Time desc so that the most recent messages are the first returned, and then it goes 'back' into the past rather than loading the oldest first.