Member Avatar for towerrounder

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:

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.

  • 2 Contributors
  • 1 Reply
  • 111 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by MeSampath
Member Avatar for MeSampath

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.