Row_Number, Rank and Dense_Rank

Syntax:

SELECT names 
        , salary 
        ,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER 
        ,rank () OVER (ORDER BY salary DESC) as RANK 
        ,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK 
FROM salaries

Output:

Row_number assign different number to them.
Rank and Dense_rank both assign same rank to A and B.
But interesting thing is what RANK and DENSE_RANK assign to next row?
Rank assign 5 to the next row, while dense_rank assign 4.

The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.  The RANK function does not always return consecutive integers.  The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. 

These all can be used with order by or partition By.


**********************************************
Row_number, Rank and Dese_Rank with Partition BY 
Example:

WITH T(Name, ID)
     AS (SELECT 'ABC',1 UNION ALL
         SELECT 'ABC',1 UNION ALL
         SELECT 'XYZ',5 UNION ALL
         SELECT 'ghj',7 union ALL
SELECT 'def',9 union ALL
SELECT 'def',9 union ALL
SELECT 'def',13 )
SELECT *,
      ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID desc) AS 'ROW_NUMBER',
       RANK() OVER(PARTITION BY Name ORDER BY ID desc)       AS 'RANK',
       DENSE_RANK() OVER(PARTITION BY Name ORDER BY ID desc) AS 'DENSE_RANK'
  FROM   T  

Output:

Another Example:


WITH T(StyleID, ID)
     AS (SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,2 union ALL
select 2,1)
SELECT *,
       RANK() OVER(PARTITION BY StyleID ORDER BY ID)       AS 'RANK',
       ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS 'ROW_NUMBER',
       DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'DENSE_RANK'
FROM   T  

Output: