RANK and DENSE_RANK SQL Server

SQL Server > Built-in Functions > RANK & DENSE_RANK

RANK returns the rank of each row within the partition of a result set

DENSE_RANK returns the rank of rows within the partition of a result set  without any gaps in the ranking

                    OVER ( [ PARTION BY ] [ORDER BY]) 
Example:

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (3,'p3', 100,1) ,   (2,'p4', 50,2)

 

--see image 1

select * from #LocalTempTable

--see image 2

SELECT i.*

    ,RANK() OVER
    ( ORDER BY i.Salary DESC) AS Rank
FROM
       #LocalTempTable i
ORDER BY i.DeptId;

--see image 3

SELECT i.*

    ,RANK() OVER
    (PARTITION BY i.DeptId  ORDER BY i.Salary DESC) AS Rank
FROM
       #LocalTempTable i
ORDER BY i.DeptId;

--see image 4

SELECT i.*

    ,DENSE_RANK() OVER
    ( ORDER BY i.Salary DESC) AS Rank
FROM
       #LocalTempTable i
ORDER BY i.DeptId;

drop table #LocalTempTable

Results:

 

Image 1 Salary Data

Image 2 RANK without PARTITION BY will rank all rows. Note the gap between 2 and 4

Image 3 RANK with PARTITION BY will rank rows in the same DeptID.

Image 2 DENSE_RANK  Note there are not gaps