I have a table with a column for hierarchy(nation, state, county) and prizes(gold, silver, bronze). I need to order it this way

Order By Asc or Desc does not achieve this. Is there a way to customize a Order by clause?

CodePudding user response：

Use CASE in the ORDER BY :

SELECT hierarchy, prize
FROM table_name
ORDER BY
  CASE hierarchy
    WHEN 'nation' THEN 1
    WHEN 'state' THEN 2
    WHEN 'county' THEN 3
    ELSE 4 END,
  CASE prize
    WHEN 'gold' THEN 1
    WHEN 'silver' THEN 2
    WHEN 'bronze' THEN 3
    ELSE 4 END

CodePudding user response：

Use case statement to map from value to order for order by clause:

-- sample data
WITH dataset (hierarchy, prize) AS (
    values ('nation', 'gold'),
        ('nation', 'silver'),
        ('nation', 'bronze'),
        ('county', 'silver'),
        ('county', 'bronze'),
        ('state', 'gold'),
        ('state', 'silver'),
        ('state', 'bronze'),
        ('county', 'gold')

) 

-- query
select *
from dataset
order by 
    case
        hierarchy
        when 'nation' then 1
        when 'state' then 2
        when 'county' then 3
        else 999
    end,
    case
        prize
        when 'gold' then 1
        when 'silver' then 2
        when 'bronze' then 3
        else 999
    end

Output: