Window functions in SQL

Window functions are most commonly used in SQL in day to day activities. So it is important to learn how they work and what are the important use cases for them.

Types of Window Functions:-

1. Ranking Functions:-
• ROW_NUMBER( )
• RANK( )
• DENSE_RANK( )
• NTILE( ) 
2. Value Window functions:-
• LEAD()
• LAG()
3. Aggregate Window functions:-
• SUM()
• MIN()
• MAX()
• COUNT()......and so on 

Important Window Functions we will cover in this tutorial :-

1. Ranking Functions:-
• ROW_NUMBER( )
• RANK( )
• DENSE_RANK( )
• NTILE( ) 
2. Value Window functions:-
• LEAD()
• LAG()

If you are more comfortable in understanding concepts through videos then you can follow the below mentioned video as well :-

1. Ranking Functions :-  Ranking functions returns a  rank for each row in the table as per the logic used.

General Syntax:- 

RANK_FUNCTION( ) OVER ([ PARTITION BY COL_1/EXPR_1, COL_2/EXPR_2  .... COL_n/ EXPR_n ] ORDER  BY   VALUE/VALUES)

WHERE:- 

• RANK_FUNCTION( )  :-  Any ranking  function (i.e. Row_number ( ), Rank( ) Dense_rank( ) or Ntile( ))

• Anything written inside  [   ]  is optional and we can skip that part if not required i.e. PARTITION BY logic is optional.

• ORDER  BY :-  Order by clause is mandatory as it helps Rank Function to decide the rank.

Now let us discuss these window functions in more details and with examples.
For all our examples we will use the following "Employee Table":-

Employee Table

1.a) ROW_NUMBER( ):-  

If partition by clause is not used:-It returns a row number for every row in the resultset produced by our query.

Example :- 

SELECT EmpNo , Ename , Salary , Dname,  ROW_NUMBER ( ) over (order by Salary)  as "row_num" FROM Employee

O/P:- We can see in output one new column has been added as "row_num" in the end.

Result of row_num() 

without partition by clause

If partition by clause is  used:- It returns a row number for every row in the particular partition in the resultset produced by our query.

Example :- 

SELECT EmpNo, Ename ,Salary, Dname,  ROW_NUMBER ( ) over (partition by Dname order by  Salary)  as "row_num" FROM Employee

O/P:- We can see in output one new column has been added as "row_num" in the end.

Result of row_num() 

with partition by clause

1.b) RANK( ):-  

If partition by clause is not used:- It returns the rank of every row in the resultset produced by our query on the basis of column used in order by clause.

But whenever more than one row has similar values under the column used in order by clause then we will get same rank for all those rows.

OR 

It returns the rank of every row in the resultset produced by our query on the basis of column used in order by clause with gaps in the rank order  (only if we get same value for more than one row).

OR in more simplest terms

Rank( ) returns same values as returned by ROW_NUMBER( ) but if  more than one row has similar values under the column used in order by clause then we will get same rank value for those rows and other things will remain same as ROW_NUMBER() function.

Example :-  Query for employee Rank on the basis of salary :-

SELECT EmpNo, Ename ,Dname, Salary ,  RANK ( ) over (order by  Salary)  as "Rank_by_Salary" FROM Employee

O/P:- We can see in output one new column has been added as "Rank_by_Salary" in the end.

Result of Rank() 

without partition by clause

If partition by clause is used:- It returns the rank of every row in the particular partition in the resultset produced by our query on the basis of column used in order by clause.

But whenever more than one row has similar values under the column used in order by clause then we will get same rank for all those rows.

OR 

It returns the rank of every row in the particular partition in the resultset produced by our query on the basis of column used in order by clause with gaps in the rank order  (only if we get same value for more than one row).

OR in more simplest terms

Rank( ) returns same values as returned by ROW_NUMBER( ) but if  more than one row has similar values under the column used in order by clause then we will get same value for those rows.

Example :-  Query for employee Rank on the basis of salary in each department:-

SELECT EmpNo, Ename ,Dname, Salary ,  RANK ( ) over ( partition by Dname order by  Salary)  as "Rank_by_Salary" FROM Employee

O/P:- We can see in output one new column has been added as "Rank_by_Salary" in the end.

 Result of Rank() 

with partition by clause

1.c) DENSE_RANK( ):-  

If partition by clause is not used:- It returns the rank of every row in the resultset produced by our query on the basis of column used in order by clause. 

But it does not include any gaps in the rank order.

Example :-  Query for employee Rank on the basis of salary:-

SELECT EmpNo, Ename ,Dname, Salary ,  DENSE_RANK ( ) over (order by  Salary)  as "Rank_by_Salary" FROM Employee

O/P:- We can see in output one new column has been added as "Rank_by_Salary" in the end.

Result of Dense_Rank() 

without partition by clause

If partition by clause is used:- It returns the rank of every row in the particular partition in the  resultset produced by our query on the basis of column used in order by clause.

But does not includes any gaps in the rank order.

Example :-  Query for employee Rank on the basis of salary in each department:-

SELECT EmpNo, Ename ,Dname, Salary ,  DENSE_RANK ( ) over ( partition by Dname order by  Salary)  as "Rank_by_Salary" FROM Employee

O/P:- We can see in output one new column has been added as "Rank_by_Salary" in the end.

Result of Dense_Rank() 

with partition by clause

Summary of Row_Number( ), Rank( ) and Dense_Rank( ):-