Question

Our Conversation table maintains many message threads. Each thread between two users has a unique id, and every time a new message is added to the thread, the message number get incremented by 1.

Here is the table with some simple data.

userid  threadid  messageid  text
--------------------------------------
1        1        1           Hello
2        1        2           How are you? 
1        1        3           Fine, and you?
4        2        1           Wanna buy my product?
5        2        2           No absolutely not!

I want to retrieve the last message of each thread in a single SQL query.

Based on the above data the result set should contain this:

userid  threadid  messageid  text
--------------------------------------
1        1        3           Fine, and you?
5        2        2           No absolutely not!

Essentially these are the last messages (with MAX messageid) in each thread.

Is this possible in SQL? If so, what does this look like?

Looking for a job?
Microsoft
Seattle

Accenture
New York

JP/Morgan Chase
Dallas, Texas

Startup (stealth)
London, UK


Related
How to pass a parameter to a SQL IN clause with C#, SQL Server
How can I prevent SQL-injection in ADO.NET?