SQL : Sort on a secondary column when first column is null

user_15077870 0
Viewed 37

I have a table with two string columns: 'type' and 'subtypes'. I want to sort my items by subtypes. However it can happen that some rows have an empty subtype. In this case, I would like to use the value in the column "type" for the sort.
For example if I have this table :

type    subtype
Bass    Plucked Bass
Bass    Hard Bass
Bass    Digital Bass
Pad     Atmosphere
Bass    
Pad     Choir


I would like the result of the sort to be :

type    subtype
Pad     Atmosphere
Bass    
Pad     Choir
Bass    Digital Bass
Bass    Hard Bass
Bass    Plucked Bass

the subtypes are sorted by alphabetic order and the only row that has an empty subtype uses its type value instead, thus "Bass" comes between "Atmosphere" and "Choir".

Is there some way to do that ? I only achieved to have all my empty types at the beginning by writing this query :

SELECT type, subtype FROM my_table ORDER BY subtype.name, type.name

I also tried this without success:

SELECT type, subtype FROM my_table ORDER BY CASE WHEN subtype.name IS NOT NULL THEN subtype.name ELSE type.name END```
sql
1 Answers

Use coalesce():

order by coalesce(subtype, type)

I'm not sure what your sample queries are trying to do. The subtype.name would just return a syntax error in almost any SQL engine.

EDIT:

Based on your comment, the values in subtype are empty strings, not NULL. You can use:

order by (case when subtype <> '' then subtype else type end)
user_11440350
Related