Reputation: 13
I can't solve this on MS Access SQL.
I have a foo table with this fields: id, title, category, date
I need to show 6 records showing the 2 most recent items from each category
For example, I have:
ID TITLE CAT DATE
------------------------
1 aaa cat1 12/03/12
2 sdfs cat2 12/03/12
3 asg cat2 13/03/12
4 flkgjfl cat3 11/03/12
5 dgdg cat1 18/03/12
6 dfgd cat1 15/03/12
7 dgdgd cat3 18/03/12
8 dfgd cat3 12/03/12
9 uiuou cat1 14/03/12
10 ghj cat4 11/03/12
So, I need something like:
ID TITLE CAT DATE
----------------------------------
5 dgdg cat1 18/03/2012
6 dfgd cat1 15/03/2012
3 asg cat2 13/03/2012
2 sdfs cat2 12/03/2012
7 dgdgd cat3 18/03/2012
8 dfgd cat3 12/03/2012
Thank you in advance.
Upvotes: 1
Views: 1388
Reputation: 91376
How about this, cat
is the name of the table.
SELECT cat.ID, cat.TITLE, cat.CAT, cat.DATE
FROM cat
WHERE cat.ID In (
SELECT TOP 2 id
FROM cat c
WHERE cat.cat = c.cat
ORDER BY [date] DESC,id)
ORDER BY cat.CAT, cat.DATE;
Upvotes: 1