Sql: Window Functions(ROW_NUMBER, RANK, DENSE_RANK)

Window functions are the functions which operates on the window(set of  rows). Window refers to the  set of rows obtained as a result of some function.

1.ROW_NUMBER :-The ROW_NUMBER function is used to generate a sequence of numbers based in a set in a specific order, in easy words, it returns the sequence number of each row inside a set in the order that you specify.
Let us have the records in table:

Query:-

Output:-

2. Rank:-  rank()  returns a rank of each row within the partition of a result set.The ranking is calculated by 1 plus the number of previous  rows.The function RANK returns the result with a GAP after a tie,Let  us have a  sampleForRankingFunctions table of database emp with following records:-

Query:-

Output:- After we run the query we will get the output as follows.

As mentioned in the definition in a group it continue to rank from previous row count therefore we get a gap and after 1 rank becomes 4 because there is a gap of three records. for designer group. Similarly for developer group we have a gap between 2 and 4 . Click for detail on rank().

DENSE_RANK :- 
Returns a rank of each row within the partition of a result set. But in this case there is no gap between the ranks. Let us use the DENSE_RANK  for above case.

Query:- 

Output:-

We can clearly notice in this case the rank continues from the  last rank. It is not affected by the row count.

We have same rank 1 for three records for the group designer but next rank is 2 instead of 4 which we get in case of rank(). Same can be be noticed for the developer group.