T-SQL – How to Select Top N Rows for Each Group

In this blog, we will learn about the usage of SQL Row_Number() function and SQL Row_Number() function is used to generate the sequential number of each row.

SQL  - The Difference between ROW_NUMBER (), RANK () and DENSE_RANK ()

Order Table:

OrderID	OrderNumber	StatusID	CustomerID	OrderAmount	OrderDate
1	11252019	1	1	$12.00	11/25/2019
2	10252019	3	1	$200.00	10/26/2019
3	10222019	3	1	$388.00	10/26/2019
4	11252019	1	2	$576.00	11/25/2019
5	11222019	3	2	$764.00	10/26/2019
6	11262019	1	3	$952.00	11/25/2019
7	11212019	3	3	$1,140.00	10/26/2019

Here are few examples of select top N rows for each group:

Example 1: we need to find most recent order of each customers

SELECT * FROM

(

Select *, ROW_NUMBER() OVER (Partition BY CustomerID ORDER BY orderDate Desc) [RowNumber] 

From dbo.ORDERS

) P

WHERE P.RowNumber = 1

Output:

OrderID	OrderNumber	StatusID	CustomerID	OrderAmount	OrderDate	RowNumber
1	11252019	1	1	$12.00	11/25/2019	1
4	11252019	1	2	$576.00	11/25/2019	1
6	11262019	1	3	$952.00	11/25/2019	1

Example 2: we need to find most recent top 2 order of each city

SQL Script:

SELECT * FROM

(

Select orders.*, Taddress.City, ROW_NUMBER() OVER (Partition BY TAddress.city ORDER BY orderDate Desc) [RowNumber]

FROM dbo.Orders inner join dbo.Tcustomers on orders.CustomerID = Tcustomers.CustomerID

inner join dbo.TAddress on Tcustomers.ShippingAddressID = Taddress.AddressID

) P

WHERE P.RowNumber <= 2

Output :

OrderID	OrderNumber	StatusID	CustomerID	OrderAmount	OrderDate	City	RowNumber
4	11252019	1	2	$576.00	11/25/2019	DUBLIN	1
1	11252019	1	1	$12.00	11/25/2019	DUBLIN	2
6	11262019	1	3	$952.00	11/25/2019	POWELL	1
7	11212019	3	3	$1,140.00	10/26/2019	POWELL	2