Rank Functions In SQL Server: Rank, ROW_NUMBER, DENSE_RANK, NTILE

Vijay Kashyap Algo Trading Expert

Understand the SQL Server Rank Functions With Examples:



In SQL Server, ranking functions are valuable for assigning a rank to each row within a specific part of a result set. They assist in determining a row's rank according to the criteria you set. Some frequently employed ranking functions in SQL Server consist of ROW_NUMBER, RANK, DENSE_RANK, and NTILE.

We have these rank functions:
  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • NTILE()

When using these functions in SQL, we use the "OVER()" clause to pick certain rows from the results. We can also use the "PARTITION BY" clause to choose a specific part of the data. Also, the "Order by" clause helps to arrange the results in either an up or down order.

We have a sample data for understanding Rank Function:

CREATE TABLE Sales (
    Product VARCHAR(50),
    Revenue FLOAT,
    Region VARCHAR(50)
);

INSERT INTO Sales (Product, Revenue, Region) 
VALUES ('Product A', 5000, 'East'),
                  ('Product B', 3000, 'West'),
                  ('Product C', 8000, 'East'),
                  ('Product D', 6000, 'West'),
                  ('Product E', 2000, 'East'),
                  ('Product F', 4000, 'West'),
                  ('Product G', 7000, 'East');



ROW_NUMBER() Function:

This function assigns a unique sequential integer to each row within a result set partition.

Example:

SELECT 
    ROW_NUMBER() OVER(ORDER BY Revenue DESC) AS RowNumber,
    Product,
    Revenue,
    Region
FROM Sales;

Output Result:

This query will generate a unique row number for each row based on the descending order of the Revenue column. The ROW_NUMBER() function will assign a sequential integer to each row, without considering any potential duplicates or ties in the data.

RANK() function:

Assigns a unique rank to each row within a result set partition, with no gaps in ranking values. If there are duplicate values, it assigns the same rank to those rows.

Example:

SELECT 
    RANK() OVER(ORDER BY Revenue DESC) AS Rank,
    Product,
    Revenue,
    Region
FROM Sales;

Output:

Here, the RANK() function will assign a unique rank to each row based on the descending order of the Revenue column. If there are duplicate values in the Revenue column, the RANK() function will assign the same rank to those rows, potentially resulting in gaps in the ranking sequence.


DENSE_RANK() Function:

Assigns a unique rank to each row within a result set partition, without any gaps in ranking values. If there are duplicate values, it assigns the same rank to those rows.

Example:

SELECT 
    DENSE_RANK() OVER(PARTITION BY Region ORDER BY Revenue DESC) AS DenseRank,
    Product,
    Revenue,
    Region
FROM Sales;

Output:
The DENSE_RANK() function will assign a unique rank to each row based on the descending order of the Revenue column. Similar to the RANK() function, if there are duplicate values in the Revenue column, the DENSE_RANK() function will assign the same rank to those rows, without any gaps in the ranking sequence.

NTILE() Function:

Divides the result set into a specified number of groups and assigns a group number to each row.

Example:

SELECT 
    NTILE(4) OVER(ORDER BY Revenue DESC) AS Quartile,
    Product,
    Revenue,
    Region
FROM Sales;

Output:
The NTILE(4) function will divide the rows into four groups, assigning a quartile number to each row based on the descending order of the Revenue column. This can help in segmenting the data into equal parts for further analysis or comparison.



Conclusion:

SQL Server provides powerful ranking functions that enable users to gain valuable insights from their data. These functions allow for the ranking of rows based on specified criteria, such as ordering by a particular column.