Ranking combination of columns with rank using Dense_Rank in SQL Server

Question

I have a requirement where I have the data as

StageID         Department Number       UserEmail

732                60012                [email protected]
733                60012                [email protected]
734                60012                [email protected]
735                60012                [email protected]
736                60012                [email protected]
737                60013                [email protected]   
738                60013                [email protected]

I want to get the output like this

StageID         Department Number       UserEmail       DRank

732              60012              [email protected]   1
733              60012              [email protected]   1
734              60012              [email protected]   1
735              60012              [email protected]   2
736              60012              [email protected]   2
737              60013              [email protected]   1
738              60013              [email protected]   1

I want the combination of DepartmentNumber and UserEmail to be considered when ranking the records. I am using DENSE_RANK() over partition to achieve this, but somehow I am not able to get my desired output. I am not sure where I am going wrong. Could anyone please help me in this

This is the query I used

SELECT StageID, DepartmentNumber, UserEmail
        ,DENSE_RANK() OVER (PARTITION BY DepartmentNumber, UserEmail
         ORDER BY DepartmentNumber, UserEmail ASC) AS DRANK 
   FROM mytable 

Answer 1

I think you want this (SQLFiddle supplied by JW):

SELECT StageID, DepartmentNumber, UserEmail
        ,DENSE_RANK() OVER (PARTITION BY DepartmentNumber
         ORDER BY UserEmail ASC) AS DRANK 
   FROM mytable 

Protip - You never want the same columns in both the PARTITION BY and ORDER BY clauses.

This is if I've understood your requirements. I think you want each departments rows to be ranked independently, and the column to select ranks as the email. If that's not your requirement, you need to be more explicit than

I want the combination of DepartmentNumber and UserEmail to be considered when ranking the records.