Hi All,

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:

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.

Few ideas;

You can try with "Group by" functionality in SQL query.

For example, the query may like

select id, [name], max(message_date) from message group by id,[name]

Thank you