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:
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:-
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().
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.
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.