Create table in SQL Server with some data
ROW_NUMBER() Function
The Row_Numaber function is an important function when you do paging in SQL Server. The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.
Syntax
ROW_NUMBER ()
OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)
OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)
OVER - Specify the order of the rows.
ORDER BY - Provide sort order for the records.
ORDER BY - Provide sort order for the records.
The ROW_NUMBER function enumerates the rows in the sort order defined in the over clause.
Example
SELECT *, ROW_NUMBER() OVER(ORDER BY User_Id) AS Row_Number
FROM userdetail
Result :
Rank() Function
The Rank function produces a rank based on the sorted order when two rows have the same order value. It provides the same rank for the two rows and also increases the same order by clause.
Syntax
RANK ( ) OVER ( [partition_by_clause] order_by_clause )
Example
SELECT *, RANK() OVER(ORDER BY User_Id) AS Rank_Number
FROM userdetail
OUTPUT
DENSE_RANK() Function
The DENSE_RANK function produces a rank based on the sorted order without any gap. When two rows have the same order value, it provides the same rank for the two rows and also increases the next row value.
Syntax
DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Example
SELECT *, DENSE_RANK() OVER(ORDER BY User_Id) AS Dense_Number
FROM userdetail
OUTPUT
NTILE() Function
Divides the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Syntax
NTILE (int_exp) OVER ([<partition_by_clause>]<order_by_clause>)
Example
SELECT *, NTILE(5) OVER (ORDER BY User_Id) AS Ntile_Number
FROM userdetail
OUTPUT
In summary
SELECT p.FirstName, p.LastName
,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"
,RANK() OVER (ORDER BY a.PostalCode) AS Rank
,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"
,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile
,s.SalesYTD
,a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
Output :
FirstName
|
LastName
|
Row Number
|
Rank
|
Dense Rank
|
Quartile
|
SalesYTD
|
PostalCode
|
|---|---|---|---|---|---|---|---|
Michael
|
Blythe
|
1
|
1
|
1
|
1
|
4557045.0459
|
98027
|
Linda
|
Mitchell
|
2
|
1
|
1
|
1
|
5200475.2313
|
98027
|
Jillian
|
Carson
|
3
|
1
|
1
|
1
|
3857163.6332
|
98027
|
Garrett
|
Vargas
|
4
|
1
|
1
|
1
|
1764938.9859
|
98027
|
Tsvi
|
Reiter
|
5
|
1
|
1
|
2
|
2811012.7151
|
98027
|
Shu
|
Ito
|
6
|
6
|
2
|
2
|
3018725.4858
|
98055
|
José
|
Saraiva
|
7
|
6
|
2
|
2
|
3189356.2465
|
98055
|
David
|
Campbell
|
8
|
6
|
2
|
3
|
3587378.4257
|
98055
|
Tete
|
Mensa-Annan
|
9
|
6
|
2
|
3
|
1931620.1835
|
98055
|
Lynn
|
Tsoflias
|
10
|
6
|
2
|
3
|
1758385.926
|
98055
|
Rachel
|
Valdez
|
11
|
6
|
2
|
4
|
2241204.0424
|
98055
|
Jae
|
Pak
|
12
|
6
|
2
|
4
|
5015682.3752
|
98055
|
Ranjit
|
Varkey Chudukatil
|
13
|
6
|
2
|
4
|
3827950.238
|
98055
|
