SQL latest/top items in category
- by drozzy
What is a scalable way to select latest 10 items from each category.
I have a schema list this:
item category updated
so I want to select 10 last update items from each category. The current solution I can come up with is to query for categories first and then issue some sort of union query:
query = none
for cat in categories:
query += select top 10 from table where category=cat order by updated
I am not sure how efficient this will be for bigger databases (1 million rows).
If there is a way to do this in one go - that would be nice.
Any help appreciated.
