• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()

July 11, 2015 by Andy Hayes 19 Comments

I’ve recently been working on a data migration project and have found myself utilizing the ROW_NUMBER() function in SQL Server a lot. This function has been around since SQL Server 2005 and at its core, provides a way to provide sequential numbering for rows returned by a query.

One of the requirements for an export process was to return the most recent two customer orders for each customer account and outputting this to CSV file.

As well as sequential numbering, ROW_NUMBER() provides a way to partition the results returned so this suited me just fine 🙂

Let’s take a look at the problem in more detail and I’m going to use our good friend the AdventureWorks database to help demonstrate.

The first query returns the data by customer and with the most recent orders for each customer at the top.

SELECT SalesOrderID
      ,OrderDate
      ,SalesOrderNumber
      ,AccountNumber
      ,CustomerID
      ,SubTotal
      ,TaxAmt
      ,TotalDue
  FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] 
ORDER BY CustomerID, OrderDate DESC

Here are the results of this query sampled.

t-sql return top n rows per group sql server

How to return the top two rows for each group in the result set?

We will use ROW_NUMBER() to place a sequential id on the results but also partition the results so that each instance of the customerID value has its sequential id reset.

SELECT SalesOrderID
      ,OrderDate
      ,SalesOrderNumber
      ,AccountNumber
      ,CustomerID
      ,SubTotal
      ,TaxAmt
      ,TotalDue
    ,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID, OrderDate DESC) AS RowNum
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]

This returns a new column called RowNum which is providing the sequential numbering for each instance of CustomerID based on the PARTITION BY and ORDER BY of the RowNum column.

t-sql return top n rows per group sql server with row_number

All that remains is to now select the first two rows for each CustomerID. So I am wrapping it up in a CTE and returning the first two rows using the WHERE clause.

WITH MyRowSet
AS
(
SELECT SalesOrderID
      ,OrderDate
      ,SalesOrderNumber
      ,AccountNumber
      ,CustomerID
      ,SubTotal
      ,TaxAmt
      ,TotalDue
    ,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID, OrderDate DESC) AS RowNum
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] 
)

SELECT * FROM MyRowSet WHERE RowNum <= 2

Which returns our desired result…

t-sql return top n rows per group sql server with row_number and cte

I hope you found this post useful. 🙂

Filed Under: All Articles, SQL Tips and Tricks Tagged With: sql server, t-sql

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Michal Pawlikowski says

    July 21, 2015 at 2:44 pm

    And if you want to skip any sorting just order by SELECT NULL:

    SELECT rown = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),*
    FROM TABLE

    Reply
    • Andy Hayes says

      July 21, 2015 at 6:12 pm

      Thank you Michal, very useful! 🙂

      Reply
  2. Hien Dang says

    November 24, 2015 at 12:34 am

    Thanks! helped me today

    Reply
  3. Faroque says

    November 10, 2017 at 8:39 am

    Very Nice. Clear & Step by Step instruction.

    Reply
  4. David says

    April 11, 2018 at 3:06 pm

    Great post. I think you could do all without a CTE by using a SELECT TOP WITH TIES:

    SELECT TOP 2 WITH TIES SalesOrderID
    ,OrderDate
    ,SalesOrderNumber
    ,AccountNumber
    ,CustomerID
    ,SubTotal
    ,TaxAmt
    ,TotalDue
    FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
    ORDER BY ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID, OrderDate DESC)

    Reply
    • Peter Moore says

      March 13, 2019 at 9:07 pm

      My tests show this is far better from a performance standpoint on a larger table

      Reply
  5. Channdeep Singh says

    May 10, 2018 at 9:04 am

    Many thanks Andy and Michal. Regards.

    Reply
  6. Ranjan says

    June 10, 2018 at 2:02 pm

    How can I pick only 3 orderdate for each date for example
    2011-05-31 00:00:00.000
    2011-05-31 00:00:00.000
    2011-05-31 00:00:00.000
    2011-06-01 00:00:00.000
    2011-06-01 00:00:00.000
    2011-06-01 00:00:00.000

    and so on also with other dates.
    I used this command but only get three of following dates: 2011-05-31 00:00:00.000 and everything of all others

    select r1.OrderDate
    from (select *, rank() over(partition by OrderDate order by (select null)) as r
    from AdventureWorks.Sales.SalesOrderHeader)r1 where r <= 3

    Reply
  7. Edoardo says

    July 17, 2018 at 6:56 am

    very useful, thanks|

    Reply
  8. Josh Christian says

    October 9, 2018 at 4:33 pm

    this helped me a lot. made it nice and simple. thanks for your help

    Reply
  9. vimal says

    October 30, 2018 at 5:05 am

    It saved my hours, perfect and to the point detail

    Reply
  10. Simon says

    November 2, 2018 at 5:38 am

    Works great!
    Thanks a lot~ 😉

    Reply
  11. gzdxtr says

    December 11, 2018 at 10:19 am

    thanks,
    it’s useful.

    Reply
  12. Baibhav Bajpai says

    March 8, 2019 at 2:31 am

    Thanks Andy! Saved my day.

    Reply
  13. Mehak says

    October 4, 2019 at 8:07 pm

    Perfect solution. Thanks.

    Reply
  14. Jared says

    January 31, 2020 at 8:54 pm

    I stumbled across this wonderful solution and it really helped me out, thank you!

    Reply
  15. JIm P says

    February 7, 2020 at 5:50 pm

    Thanks for taking the time to post. Got me going in the right direction.

    Reply
  16. Al says

    June 18, 2020 at 3:45 pm

    You save my day thanks lot , you deserve cup of coffee 🙂

    Reply
  17. Dale Jordaan says

    October 15, 2020 at 10:28 am

    This helped me so much!

    thanks

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©