365 Days of Daily Coding: Day 109

When will ROW_NUMBER and RANK give different results? Give an example.

Both the functions ROW_NUMBER and RANK return an integer value that are incremental in nature. ROW_NUMBER as the name suggests assigns an incremental number to each subsequent rows whereas RANK functions ranks the records by values imposed by the ORDER BY clause.

However, both these functions differ when there are records that are tied in values of the columns stated in the ORDER BY clause.

When records are tied, ROW_NUMBER will simply return the corresponding row number whereas RANK will return the values of the previous row in case of a tie and also skip the rank.