Monday, November 25, 2019

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.


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


No comments:

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...