How to order by one column, but rank based on a different column that is not numeric?

Question

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

Answer 1

You can use LAG() to check if the previous city is the same. If the previous city is different or null then we take rank as it is, if cities are same then rank - 1 gives us the same number as row above. Demo

with cte as (select Name, Employee_ID, Order_Number, City,
dense_rank() over (partition by Employee_ID order by Order_Number) as rank,
lag(City) over (partition by Employee_ID order by Order_Number) as previousCity
from #Employee)
select 
    Name, Employee_ID, Order_Number, City,
    case when previousCity = city then rank - 1
         else rank end as rank
from cte

Answer 2

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.