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:
Post a Comment