CODEWITHSUNDEEP
sqlsql-serverrank
Stuart1044
Stuart1044

Reputation: 444

SQL Rank() for multiple conditions

I have a set of data that stores employee and their takings for a period (a month say). The data also stores multiple sites, and the employees can have takings for each site. I am trying to add a rank to the data, so that each employee has a rank based on the takings, for each site.

Example Data

EmpId   Takings SiteID
1       150.00  1
1       0.00    2
2       0.00    1
2       100.00  2
3       100.00  1
3       1100.00 2

I have tried the below query but this only gives me ranks up to two:

SELECT EmpId,Takings,SiteID, RANK() OVER (PARTITION BY EmpId ORDER BY Takings DESC) AS [Rank]
FROM #test

This gives me the following result set:

EmpId   Takings SiteID  Rank
1       150.00  1       1
1       0.00    2       2
2       100.00  2       1
2       0.00    1       2
3       1100.00 2       1
3       100.00  1       2

I am expecting the following result set:

EmpId   Takings SiteID  Rank
1       150.00  1       1
1       0.00    2       3
2       100.00  2       2
2       0.00    1       3
3       1100.00 2       1
3       100.00  1       2

If I amend the Partition to include SiteId, then I get a rank of 1 for all employees.

What am I missing here, I am sure this should be a simple query, but it's killing me at the moment.

Thank You

Upvotes: 0

Views: 7969

Answers (2)

IDon'tKNowWhatIDo
IDon'tKNowWhatIDo

Reputation: 1

By simply adding the SiteID to the partition worked for me

SELECT EmpId,
       Takings,
       SiteID,
       RANK() OVER (PARTITION BY EmpId, SiteID ORDER BY Takings DESC) AS [Rank]
FROM #test

Upvotes: 0

ken lacoste
ken lacoste

Reputation: 894

SELECT EmpId,Takings,SiteID, ROW_NUMBER() OVER (PARTITION BY SiteId ORDER BY Takings DESC) AS [Rank]
FROM #test
ORDER BY EmpId ASC, Takings DESC

Hopefully this one?

Upvotes: 3

Related Questions