select top 10 records for each category.

Question

I want to return top 10 records from each section in the one query. Can anyone help how to do it. Section is one of the column in the table.

Database is sql server 2005. Top 10 by date entered. Sections are business, local and feature For one particular date I want only top(10) business rows (most recent entry), top (10 ) local rows and top (10) features for one particular date.

Answer 1

Could you elaborate a bit more about this? For one, what's the database (e.g. MySQL, Oracle, MS SQL)? When you say top 10 are we talking about value or most recently entered?

Answer 2

Might the UNION operator work for you? Have one SELECT for each section, then UNION them together. Guess it would only work for a fixed number of sections though.

Answer 3

This works on SQL Server 2005 (edited to reflect your clarification):

select *
from Things t
where t.ThingID in (
    select top 10 ThingID
    from Things tt
    where tt.Section = t.Section and tt.ThingDate = @Date
    order by tt.DateEntered desc
    )
    and t.ThingDate = @Date
order by Section, DateEntered desc

Answer 4

If you know what the sections are, you can do:

select top 10 * from table where section=1
union
select top 10 * from table where section=2
union
select top 10 * from table where section=3

Answer 5

If you are using SQL 2005 you can do something like this...

SELECT Field1,Field2 
    FROM (
        SELECT Field1,Field2, Rank() over (Partition BY Section ORDER BY RankCriteria DESC ) AS Rank
        FROM table
        ) rs WHERE Rank <= 10)

If your RankCriteria has ties then you may return more than 10 rows and Matt's solution may be better for you.

Answer 6

I do it this way:

SELECT a.* FROM articles AS a
  LEFT JOIN articles AS a2 
    ON a.section = a2.section AND a.article_date <= a2.article_date
GROUP BY a.article_id
HAVING COUNT(*) <= 10;

---

update: This example of GROUP BY works in MySQL and SQLite only, because those databases are more permissive than standard SQL regarding GROUP BY. Most SQL implementations require that all columns in the select-list that aren't part of an aggregate expression are also in the GROUP BY.