Left join SQL Server return result based on ranking

Question

I have a one to many left join in SQL Server 2008 and need to return a row based on a priority assigned in the query. For the sake of clarity, assume that A is the most important, followed by B, C...

In my table these varchar fields are words, and cannot be ordered alphabetically, and so (I think) I need to assign a numerical value to each.

Table1
Station 1
Station 2
Station 3


Table2 
Station 1   User 1  A
Station 1   User 2  B
Station 1   User 3  A
Station 1   User 4  B
Station 1   User 5  B
Station 2   User 1  B
Station 2   User 2  F
Station 2   User 3  D
Station 2   User 4  S
Station 2   User 5  E
Station 3   User 1  D
Station 3   User 2  D
Station 3   User 3  D
Station 3   User 4  S
Station 3   User 5  C

I'd like to return the results below:

Station 1   A
Station 2   B
Station 3   C

Ie A is the most important for station 1, B for Station 2 etc.

My logic so far is to use a case, min and select in statement:

select t1.station
, min(
case 
    when A then 1
    when B then 2
    when C then 3
    when D then 4
end
)
from table1.t1 left join table2.t2 on t1.station = t2.station
group by t1.station

However, since I'm returning multiple lines with the join, I'm not sure how to deal with this, I also wish to return the string, not the priority assigned. I'd appreciate some help on this, Thanks in advance

Answer 1

You can use the CASE expression in the ORDER BY clause of a ROW_NUMBER function:

select station, mycol
from (
   select t1.station, t2.mycol,
          ROW_NUMBER() OVER (PARTITION BY t1.station
                             ORDER BY CASE t2.mycol
                                        WHEN A THEN 1
                                        WHEN B THEN 2
                                        WHEN C THEN 3
                                        WHEN D THEN 4
                                      end) AS rn
   from table1.t1 
   left join table2.t2 on t1.station = t2.station) as t
where t.rn = 1

The query will pick the record having the greatest priority within each t1.station partition. It also returns the other field from table2.