Select top 10 records for each category

sql sql-server sql-server-2005 greatest-n-per-group
jbcedge picture jbcedge · Oct 7, 2008 · Viewed 495.8k times · Source

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

Database is SQL Server 2005. I want to return the top 10 by date entered. Sections are business, local, and feature. For one particular date I want only the top (10) business rows (most recent entry), the top (10) local rows, and the top (10) features.

Answer

Darrel Miller picture Darrel Miller · Oct 7, 2008

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

SELECT rs.Field1,rs.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.

Related questions

SQL server select distinct rows using most recent value only

I have a table that has the following columns Id ForeignKeyId AttributeName AttributeValue Created Some of the data may look like this: 1, 1, 'EmailPreference', 'Text', 1/1/2010 2, 1, 'EmailPreference', 'Html', 1/3/2010 3, 1, 'EmailPreference', 'Text', 1/10/2010 4, 2, 'EmailPreference', 'Text', 1/2/2010 5, 2, 'EmailPreference', 'Html', 1/8/2010 I'd like to run a query that pulls …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More