Ranking functions –part1

Ranking functions are used to provide simple analytics such as statistical ordering or segmentation.
T-SQL has four functions that can be used for ranking data: ROW_NUMBER, RANK,
DENSE_RANK, and NTILE.

ROW_NUMBER: This function returns a sequential number starting at 1 for each row or grouping within your result set.
Let's see examples:



Also you can group and order each group as a separate block result set, let's see this example:



RANK: If you need to number a result set but also deal with values that have the same order by, you can use the RANK function. If the result set does not need to have the same order by, RANK produces the same results as ROW_NUMBER.
RANK assigns the same value to each row that is has the save order by and then skips to the next value, leaving a gap in the sequence corresponding to the number of rows that were tied.
Let's see an example:



And likewise ROW_NUMBER if you want to use PARTITION BY with Grouping.