'how do I sort a presto sql output in a custom order?

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

hierarchy prize
nation gold
nation silver
nation bronze
state gold
state silver
state bronze
county gold
county silver
county bronze

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

sqlpresto


Solution 1:[1]

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

Solution 2:[2]

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:

hierarchy prize
nation gold
nation silver
nation bronze
state gold
state silver
state bronze
county gold
county silver
county bronze

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Guru Stron

Related Questions