SQL latest/top items in category

Question

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:

categories = sql.execute("select categories from categories_table")
query = ""
for cat in categories:
   query += "union select top 10 from table where category=cat order by updated"
result = sql.execute(query)

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.

Answer 1

This will not compile but you'll have the general idea:

from i in table
group i by i.category into g
select new { cat = g.Key, LastTens = g.OrderByDescending(o => o.Updated).Take(10).Select(...) }

EDIT: the question asked for SQL:

SELECT * FROM 
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY categoryId ORDER BY somedate) AS PartNum,
        categoryId,
            [...]
    FROM
        category
) AS Partitionned
WHERE PartNum <= 10