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()

by 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. 🙂

Related Posts:

Reader Interactions

Comments

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

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

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

Leave a Reply

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