These functions are categorized into types of "Window Functions".
Window Functions were first introduced in SQL Server 2005 and greatly expanded upon since SQL Server 2012 product release.
SQL Server 2012+ now includes three types of window functions: Ranking, Aggregate, Analytic.
Code Example of T-SQL Ranking Functions:
Create & Populate a table with data:
Create Table #TBL (League varchar(100), Team varchar(100), Salary int)
Insert into #TBL Values ('National League', 'Los Angeles Dodgers', 272000000)
Insert into #TBL Values ('American League', 'New York Yankees', 219000000)
Insert into #TBL Values ('American League','Boston Red Sox', 191000000)
Insert into #TBL Values ('American League','Detroit Tigers', 191000000)
Insert into #TBL Values ('National League','San Francisco Giants', 191000000)
Insert into #TBL Values ('National League','Washington Nationals', 174000000)
Insert into #TBL Values ('National League','Los Angeles Angels', 164000000)
Insert into #TBL Values ('American League','Texas Rangers', 142000000)
Insert into #TBL Values ('National League', 'Philadelphia Phillies', 135000000)
Insert into #TBL Values ('American League', 'Toronto Blue Jays', 121000000)
Query using Ranking Functions:
SELECT
League,
Team,
Salary,
ROW_NUMBER() OVER(PARTITION BY League ORDER BY Salary DESC) AS RowNumber,
RANK() OVER(PARTITION BY League ORDER BY Salary DESC) AS BasicRank,
DENSE_RANK() OVER(PARTITION BY League ORDER BY Salary DESC) AS DenseRank,
NTILE(3) OVER(PARTITION BY League ORDER BY Salary DESC) AS NTileRank
FROM
#TBL;
Drop Table #TBL
Each Ranking Function ranks the data based on the Partition Clause and sorted Salary values. Each does it differently though.
ROW_NUMBER() function numbers each row sequentially in the partition. Two patitions exist above - 'National League' and 'American League'
RANK() function numbers each row in the partition. Duplicate values are assigned the same rank value. It skips the rank value that would have been assigned to the second duplicate.
DENSE_RANK() numbers each row in the partition. Duplicate values are assigned the same rank value. It does not skip ranking values.
NTILE() distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. Example aboue: A value of 3 is passed in as an argument to the function. Thus the function divides the partition into three groups (Total Rows / 3). The function assigns a value of 1 to each row in the first group, a value of 2 to each row in the second group and a value ood 3 to each row in the third group.
Resources:
ROW_NUMBER (Transact-SQL)
RANK (Transact-SQL)
DENSE_RANK (Transact-SQL)
NTILE (Transact-SQL)
|
|
|
|
|
|