I have four columns that I am trying to rank. They need to be grouped by employee ID and then listed low to high by order number. Then when everything is in order, I'm really trying to get the ranking of where the city falls in that order. If the same city is listed after another for the same employee then I want that those ranked the same.
An example of the table is below. The order is correct, but the ranking is not for what I'm trying to do.
Name Employee_ID Order_Number City Rank
John 1 1 Boston 1
John 1 2 Boston 2
Will 2 1 Peabody 1
Will 2 2 Weston 2
Will 2 3 Newton 3
select Name, Employee_ID, Order_Number, City,
dense_rank() over(partition by Employee_ID order by Order_Number) as rank
from #Employee
How I would actually want the results are:
Name Employee_ID Order_Number City Rank
John 1 1 Boston 1
John 1 2 Boston 1
Will 2 1 Boston 1
Will 2 2 Weston 2
Will 2 3 Newton 3
Then I would eventually remove the duplicate Cities to end up with:
Name Employee_ID Order_Number City Rank
John 1 1 Boston 1
Will 2 1 Boston 1
Will 2 2 Weston 2
Will 2 3 Newton 3
You can try this following script to get your desired output.
SELECT Name, Employee_ID, Order_Number, City ,
ROW_NUMBER() OVER (PARTITION BY Employee_ID ORDER BY Order_Number) rank
(
select Name, Employee_ID, Order_Number, City,
dense_rank() over(partition by Employee_ID,city order by Order_Number) as rank
from #Employee
)A
WHERE rank = 1
Output from your result set is-
Name Employee_ID Order_Number City rank
John 1 1 Boston 1
Will 2 1 Peabody 1
Will 2 2 Weston 2
Will 2 3 Newton 3
Check output of the script on Fiddle.