Row_Number, Rank, DenseRank, and NTILE

Shristi
Shristi
Nov 4 · 3 min read

Example Table

Employee Table

Row_Number, Rank, Dense_Rank, and NTILE are the windows function that is used to assign the numbers to rows of data present in the table.

Windows functions are used to calculate a value based on a group of rows and return multiple rows for each group.

These functions work with the order by function to number the rows of a table in the database.

Partition By clause is used to differentiate the data into the partition so that window function operated on individually on each partition and calculate every partition separately.

Row_Number()

This function is a window function used to number the row of a table in a sequential manner according to the Order By function.

The numbering for each partition in the row starts with one

The Row_Number function will give a unique value to each row in the table irrespective of the data

Example

SELECT

ROW_NUMBER() OVER (ORDER BY Name) Num_of_Employees

, Name

, Address

, phone

, Salary

FROM Employee;

Output

Rank()

A Rank() function is used to assign a rank to the records in the table.

The Rank() function will give the same rank to the data which are same i.e the similar values will have the same rank

The rank within the partition starts with one

In this function, we can see the rank number is repeated the number of times equal to the number values repeated within a table

Example

SELECT RANK() OVER (ORDER BY Name )

AS rank_nums

,Name

,Address

,phone

,Salary

FROM Employee;

Output

Dense_rank()

The Dense_rank()is used to rank the rows in the table

Dense_Rank() function which is similar to the Rank() function but not totally the Rank() gives similar rank to the same values in the table whereas the Dense_Rank() gives different rank to the same values irrespective of the data.

This function assigns the rank sequentially to each record in the partition.

Example

SELECT DENSE_RANK() OVER (ORDER BY Name)

AS Denserank_nums

,Name

,Address

,phone

,Salary

FROM Employee;

Output

NTILE()

The NTile function divides the records of the table according to the integer which is passed as a parameter in the NTILE

The NTile function for example. NTile(3) if we pass 3as a parameter the records will be divided into group of four.

Example

SELECT NTile(4) OVER (ORDER BY Name )

AS NTile_rank

,Name

,Address

,phone

,Salary

FROM Employee

Output

If you want to more about Row_Number, Rank, and Dense_Rank function do watch the following video:-

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade