I'm having trouble formulating a query. I think I'm trying to be too clever here though
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!
FROM ( SELECT Category
, MAX(Date) AS maxdate
BY Category ) AS m
JOIN daTable AS t
ON t.Category = m.Category
AND t.Date = m.maxdate