I'm trying to implement single-column regionalization for a Rails application and I'm running into some major headaches with a complex SQL need. For this system, a region can be represented by a country code (e.g. us) a continent code that is uppercase (e.g. NA) or it can be NULL indicating the "default" information. I need to group these items by some relevant information such as a foreign key (we'll call it external_id).

Given a country and its continent, I need to be able to select only the most specific region available. So if records exist with the country code, I select them. If, not I want a records with the continent code. If not that, I want records with a NULL code so I can receive the default values.

So far I've figured that I may be able to use a generated CASE statement to get an arbitrary sort order. Something like this:

SELECT *, CASE region 
            WHEN 'us' THEN 1 
            WHEN 'NA' THEN 2 
            ELSE 3 
          END AS region_sort 
          FROM my_table 
          WHERE region IN ('us','NA') OR region IS NULL 
          GROUP BY external_id 
          ORDER BY region_sort

The problem is that without an aggregate function the actual data returned by the GROUP BY for a given row seems to be untameable. How can I massage this query to make it return only the first record of the region_sort ordered groups?