This article describes how to use the T-SQL Ranking functions in SQL Server database.
Ranking functions are inbuilt methods that return a ranking value based on each row of data. They are non-deterministic as based on the row data, the function output differs from one another which can be identical for certain rows or contradictory as well.

Ranking functions are a type of function that assigns a numeric value to each row in a result set, with the numeric value being based on the position of that row within the result set.

Ranking functions are often used to find out how well something is doing in comparison to other things. There are four ranking functions in SQL Server database: RANK, DENSE_RANK, NTILE, and ROW_NUMBER.

RANK

RANK assigns a rank to each row in a result set. The rank is based on the order of the rows in the result set. Rows with the same value for the ranking column will receive the same rank.

The SQL Server RANK function returns a unique rank number based on a distinct or unique row. The ranking function starts at 1 from the first row. If there are identical rows to each other, it displays the same rank identification and displays a gap after the rank.

SELECT
RANK() OVER(ORDER BY price, name) AS RowNum,
price, name
FROM courses;

DENSE_RANK

DENSE_RANK also assigns a rank to each row in a result set, but the ranks are assigned consecutively, without any gaps. So if there are two rows with the same value for the ranking column, they will receive the same rank. The SQL Server functions RANK and DENSE_RANK are quite similar, but the only difference is that DENSE_RANK doesn't leave any gaps or skip ranks.

SELECT name, price,
DENSE_RANK() OVER (PARTITION BY price ORDER BY name) AS RowNum
FROM courses
WHERE price BETWEEN 50 AND 100;

NTILE

The SQL Server NTILE function distributes the rows based on a specific number of groups assigned. Each row has a unique group number and the group numbering starts from 1.
NTILE assigns a certain number of rows to each group (or "bucket"). For example, if you have 10 rows in a result set and use NTILE(5), each group will contain 2 rows.

SELECT
NTILE(5) OVER(ORDER BY price, name) AS RowNum,
price, name
FROM courses;

ROW_NUMBER

ROW_NUMBER assigns a sequential number to each row in a result set. The numbers are assigned based on the order of the rows in the result set.
The SQL Server ROW_NUMBER function returns a unique sequential number for each row within the specified partition. The numbering order starts with 1 from the first row. Suppose that if the row set has duplicate values, it is assigned an arbitrarily ranked identification. To assign serial numbers, the ROW_NUMBER function comes in handy.

SELECT name, price,
ROW_NUMBER() OVER (PARTITION BY price ORDER BY name) AS RowNum
FROM courses;