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