September 06, 2008

SELECT TOP N Rows Per Group/Category




If you would like to select top 'n' rows for each group or category, then here is a query to do so :

-- Create Sample Table
DECLARE @TT table
( ID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 1, 1, 'AA')
INSERT INTO @TT VALUES ( 2, 1, 'AA');
INSERT INTO @TT VALUES ( 3, 1, 'AA');
INSERT INTO @TT VALUES ( 4, 2, 'BB');
INSERT INTO @TT VALUES ( 5, 2, 'BB');
INSERT INTO @TT VALUES ( 6, 2, 'BB');
INSERT INTO @TT VALUES ( 7, 2, 'BB');
INSERT INTO @TT VALUES ( 8, 3, 'CC');
INSERT INTO @TT VALUES ( 9, 3, 'CC');
INSERT INTO @TT VALUES ( 10, 3, 'CC');
INSERT INTO @TT VALUES ( 11, 3, 'CC');
INSERT INTO @TT VALUES ( 12, 3, 'CC');
INSERT INTO @TT VALUES ( 13, 4, 'DD');

-- Query to retrieve top 3 items per category
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CategoryGroupID ORDER BY ID) AS 'RowNo',
ID, CategoryGroupID, CategoryGroupName
FROM @TT
)
SELECT ID,CategoryGroupID,CategoryGroupName
FROM CTE
WHERE RowNo <= 3

-- Expected Output
1 1 AA
2 1 AA
3 1 AA
4 2 BB
5 2 BB
6 2 BB
8 3 CC
9 3 CC
10 3 CC
13 4 DD

To apply this on a real table, we will select the top 3 products by quantity for each category in the Northwind database

-- TOP 3 Products By Quantity for each Category
USE NORTHWIND;

WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CategoryName ORDER BY UnitsInStock DESC) AS 'RowNo',
CategoryName, ProductName, UnitsInStock FROM Categories c
INNER JOIN Products p ON c.CategoryID = p.CategoryID
)
SELECT CategoryName, ProductName, UnitsInStock FROM CTE
WHERE RowNo <= 3


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

19 Responses to "SELECT TOP N Rows Per Group/Category"
  1. Anonymous said...
    October 19, 2008 at 5:45 PM

    PARTITION BY was the key I was looking for. Thanks!

  2. Anonymous said...
    February 9, 2009 at 12:59 PM

    This was the most lucid, clear explanation of this process. Worked like a charm, and save me hours (and probably a few torn-out hairs).

    Thank.s

  3. Suprotim Agarwal said...
    February 10, 2009 at 6:13 PM

    I am Glad it helped you out!

  4. phaedrus said...
    February 11, 2009 at 4:49 PM

    This is EXACTLY the kind of technique I have been looking for. Thank you very much for sharing it!

  5. Anonymous said...
    March 3, 2009 at 10:17 AM

    Thanks
    Its so nice, I dindt know about all this,
    good one

  6. Anonymous said...
    April 1, 2009 at 12:48 PM

    very useful! thanx!

  7. Chris said...
    November 15, 2009 at 10:22 PM

    Thank you so much! I'm working on a project for school and this was so helpful. Great explanation as I just could not wrap my head around some of the other examples I had found!

  8. Anonymous said...
    December 14, 2009 at 6:59 AM

    Good job with this.

  9. Madhivanan said...
    February 3, 2010 at 6:03 AM

    Here are some other methods
    http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

    Madhivanan

  10. Anonymous said...
    March 25, 2010 at 8:37 AM

    Great post, thanks. I added a variation that allows you to sort the CTE query. Here, I sorted by "Priority"

    WITH CTE AS
    (
    SELECT top 250000 ROW_NUMBER() OVER (PARTITION BY CompanyName ORDER BY ID) AS 'RowNo',
    ID, CompanyName, Email, Priority
    FROM ABCTABLE
    order by priority desc
    )

    Delete from ABCTABLE where email in (
    SELECT Email
    FROM CTE
    WHERE RowNo > 5)


    ... This let me take a company where we have multiple contacts and narrow down to the top 5, by priority.

    Note: The TOP 250000 - to do the ORDER BY you need a TOP parameter. I picked 250,000 because the table had 175,000 records.

  11. rax said...
    June 2, 2010 at 1:04 AM

    if i want to get the results post the query and place into a #tempTable, how would i append the script?

  12. Anonymous said...
    June 2, 2010 at 1:27 AM

    realised neeeded to place a select into the last part...

    WITH CTE AS
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY locid ORDER BY loccvgid) AS 'RowNo',
    loccvgid, locid
    FROM loccvg
    )
    SELECT
    LOCCVGID, locid
    into #t
    FROM CTE
    WHERE RowNo <= 1

  13. Anonymous said...
    October 13, 2010 at 2:25 PM

    GENIUS! exactly what I needed and worked perfectly! THANK YOU!!

  14. Anonymous said...
    February 2, 2011 at 3:31 AM

    Fantastic, thank you...JUST what I needed.

  15. odanyboy said...
    February 3, 2011 at 9:22 AM

    Cheers for that ... I got just what I needed after looking for ages.

  16. Anonymous said...
    August 12, 2011 at 1:44 PM

    This is a great, clear, and to the point post. Thanks!

  17. Anonymous said...
    December 25, 2011 at 1:26 PM

    After many hours searching and trying 'solutions', I finally managed to find something that actually works and I can easily understand.

    Thanks! :)

  18. GirishChhatani said...
    February 22, 2012 at 3:50 AM

    is it possible without using Row number function the reason i am looking to implement some other logic bcse Row_number() query which i am firing on a History table which contains lakhs of records... so query takes long time to execute.

    So pls help me out...
    Thanks & Regards,
    Girish Chhatani

  19. GirishChhatani said...
    February 22, 2012 at 7:07 AM

    I have a History Table tbl_history which contains crores of Records.

    Table Sample Data:-
    id Volume DeletedTime
    1 C 11am
    1 C 12am
    1 C 1pm
    1 C 2pm
    1 C 3pm
    1 D 11am
    1 D 12am
    1 D 1pm
    1 D 2pm
    1 D 3pm
    2 C 11am
    2 C 12am
    2 C 1pm
    2 C 2pm
    2 C 3pm
    2 D 11am
    2 D 12am
    2 D 1pm
    2 D 2pm
    2 D 3pm

    Now what i want is to list out last 3 Records per ID and per Category i.e in our Case Volume, without using Row number function the reason i am looking to implement some other logic other than Row_number() because query takes long time to execute.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions