Q:

Most optimal way to prioritize records based on bit columns

There is a 1 to many relationship between tables GeoNames (not pictured) and GeoNameAlternateNames (shown below) by a FK GeoNameId.  Given the below structure, I want to get the alternateName value where IsPreferredName = 1, if that is not found then use the next column IsShortName, if that record isn't found then use the next column and so on and so on.  In this instance I would expect The top row to be returned.  Please keep in mind that I will be joining into this table from a GeoNames table.
There are 11 million+ records in GeoNames and 17 million+ in GeoNamesAlternateNames so going at it like a neanderthal is not an acceptable solution.
What is the best performing strategy around this?  I've tried group by, left joining on each column and coalescing, Ranking into sub tables, and I'm about to pull out my hair.  Thanks in advance.

A:

This is a prioritization query.  You want one row per geonameid, so use row_number() with an appropriate order by clause:
select t.*
from (select t.*,
             row_number() over (partition by geonameid
                                order by (case when IsPreferredName = 1 then 1
                                               when IsShortName = 1 then 2
                                               . . .
                                          end)
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

