tom preston tom preston - 1 year ago 52
SQL Question

Left join SQL Server return result based on ranking

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.

Station 1
Station 2
Station 3

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(
when A then 1
when B then 2
when C then 3
when D then 4
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 Source

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 
                                        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.