Ranking functions are used to rank each row of data based on their relative position to the sections assigned or the whole data table. SQL supports 4 different types of ranking functions as shown below and all ranking function output are positive integers starting from 1:

Rank: the most commonly used ranking function. It assigns a ranking number to each row of data based on the column and order that user specifies. If multiple values in the column are the same, they would receive the same rank output and the next value in the order will skip the next few values to fill the true rank in the column. For example, if two values are the same and they are rank 3 in the group, then the next value in the order will be assigned 5.

Dense_Rank: the idea for dense_rank is the same as rank before. The only difference is that next value in the order will not skip even if previous few are the same. For example, if two values are the same and they are rank 3, the next value in the order will be assigned 4.

Row_Number: Sequential number starting from 1 to each row in the group specified

**Ntile: **User needs to specify the N which is the number of groups that they want to divide the data group. The data will be evenly split and the output number is assigned based on the value range that it falls under.

Syntax for all 4 different ranking functions is very similar:

() Over ( Partition by  Order by )

here refers to the 4 different rank functions as shown above. Bracket after ‘Over’ defines the way you want to rank it. “Order by” clause in bracket is required and it defines which column you want to rank the data on. You can put ASC or DESC after the for ascending or descending order(default is descending). The “Partition by” clause is optional and it defines the sub-category that you want to rank the data on. For example, you want to rank the salary of employee based on their respective department so you should partition by department. Order by and Partition by can be used in other SQL windows function as well.

#rankings #data #window-functions #data-engineering #sql #data analysis