Ranking Window Functions in Sql Server

RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

To explain ranking window function let consider the implementation

create table scores(

id integer not null primary key identity(1,1),

score float);

insert into scores(score)values(3.50),(3.65),(4.00),(3.85),(4.00),(3.65);

select * from scores;

RANK():-The RANK() function is used to give a unique rank to each record based on a specified value, for example salary, order amount etc. If two records have the same value then the RANK() function will assign the same rank to both records by skipping the next rank.

This means — if there are two identical values at rank 2, it will assign the same rank 2 to both records and then skip rank 3 and assign rank 4 to the next record.

select RANK()over(order by score desc) as rank, score from scores order by score desc;

Result from the above query

DENSE_RANK():-The DENSE_RANK() function is identical to the RANK() function except that it does not skip any rank.

This means that if two identical records are found then DENSE_RANK() will assign the same rank to both records but not skip then skip the next rank.

select DENSE_RANK()over(order by score desc) as dense_rank, score from scores order by score desc;

Result from the above query

ROW_NUMBER():-These functions assign a unique row number to each record.

ROW_NUMBER():- without PARTITION BY

select ROW_NUMBER()over(order by score desc) as row_num, score from scores order by score desc;

Result from the above query

ROW_NUMBER():- with PARTITION BY

select ROW_NUMBER()over(partition by score order by score desc) as row_num, score from scores order by score desc;

Result from the above query

NTILE:-NTILE() is a very helpful window function. It helps you to identify what percentile (or quartile, or any other subdivision) a given row falls into.

This means that if you have 100 rows and you want to create 4 quartiles based on a specified value field you can do so easily and see how many rows fall into each quartile.

select Ntile(3)over(order by score desc) as Ntile_function, score from scores order by score desc;

Result from the above query

Database Engineer having working experience in SQL and NO-SQL databases. 👨‍💻