I have a query that returns rows from a message table. Im basically trying to get the latest MESSAGE_TYPE for the user based on when the message was sent (MESSAGEDATE)
At the moment I have a query that gets me almost what I want. It returns something like this:
ID MESSAGE_DATE NAME MESSAGE TYPE
1 28 August 2010 Bob RECEIVED
1 27 August 2010 Bob SENT
1 26 August 2010 Bob RECEIVED
2 25 August 2010 Mick SENT
3 29 August 2010 Jon RECEIVED
Focusing on the top 3 rows (and taking the date into account) you can see Bob Received the last message.
How do I return the table with the top three rows merged into one. I only want to see Bob's latest MESSAGE TYPE base on the most recent date - i.e his last action (Whether he sent the last message or received it)
Thank for the help.