Find Last Posted Message associated with each category

Hi guys,

I’m having trouble formulating a query. I think I’m trying to be too clever here though :slight_smile:

I have a table, which has an id (Primary Key, Auto Incrmement), Category (varchar), Message (varchar), Date (datetime).

So assuming I have the following in the table:-


id |Category |Message      |Date*     |
---|---------|-------------|----------|
1  |News     |News Item 1  |Yesterday |
2  |Sport    |Sport Item 1 |2 Days Ago|
3  |Music    |Music Item 1 |3 Days Ago|
4  |Sport    |Sport Item 2 |Yesterday |
5  |News     |News Item 2  |Today     |
6  |Sport    |Sport Item 3 |Today     |

(assuming date is the proper field, just showing this for this example)

What I am after is a query so that it returns the most recent result in each category (so in my example it’d be id’s 3,5,6).

I’ve tried using distinct, but can’t seem to get my head around it. I’m assuming that I can create a separate table that has a list of all the categories & matches up the category to the ID, but just wondering if it’s possible using one table.

Any suggestions on where to start? I’d like to say I’m average at MySQL, so once I have the ball rolling, I can problably help, it’s just getting started that’s annoying me!

Cheers :slight_smile:

SELECT t.id
     , t.Category
     , t.Message     
     , t.Date
  FROM ( SELECT Category
              , MAX(Date) AS maxdate
           FROM daTable
         GROUP
             BY Category ) AS m
INNER
  JOIN daTable AS t
    ON t.Category = m.Category
   AND t.Date = m.maxdate

:slight_smile:

That’s perfect thank you :slight_smile: