SQL Rank

 SQL RANK:

          Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

There are four functions

• RANK
• DENSE_RANK
• ROW_NUMBER
• NTILE

RANK  

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

DENSE_RANK

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

ROW_NUMBER
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
NTILE

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

Difference between the RANK and DENSE_RANK
RANK leaves gaps while ranking the records whereas DENSE_RANK doesn’t leave any gaps and always have consecutive ranks.
 
Example:

select price,
ROW_NUMBER() over(order by price) as 'Row Number',
RANK() over(order by price) as 'Rank',
DENSE_RANK() over (order by price) as 'Dense Rank',
NTILE(5) over (order by price) as 'Partition'
 from tbl_price

Output:

price    Row Number    Rank    Dense Rank    Partition
5           1                     1               1                  1
5           2                     1               1                  1
6           3                     3               2                  1
10         4                     4               3                  2
15          5                    5               4                  2
25         6                     6               5                  2
50         7                     7                6                 3
90         8                     8                7                 3
90        9                      8                7                 4
90        10                    8                7                 4
95       11                     11               8                 5
100     12                     12               9                 5