select top 10 records for each category.

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.

From stackoverflow jazzrai

• 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?

  From Russell Myers

• 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.

  From sblundy

• 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
  

  Matt Hamilton : This doesn't work for rows where Section is null, though. You'd need to say "where (tt.Section is null and t.Section is null) or tt.Section = t.Section"
  From Matt Hamilton

• 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
  

  hectorsosajr : This would be the easiest way of doing it.
  From Blorgbeard

• 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.

  jop : +1 -- I was about to post this very same answer. I've tried it on oracle.

  Mike L : If you really just want the top 10, change it to RowNumber() instead of Rank(). No ties then.
  From Darrel Miller

• 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.

  Blorgbeard : Does that work? I'm pretty sure you'd "a.somecolumn is invalid in the select list as it is not contained in an aggregate function or the group by clause" for every column in articles except article_id..

  Bill Karwin : You should be able to include other columns that are functionally dependent on the column(s) named in the GROUP BY. Columns that are not functionally dependent are ambiguous. But you're right, depending on RDBMS implementation. It works in MySQL but IIRC fails in InterBase/Firebird.
  From Bill Karwin