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

Question

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```

Gordon Linoff · Accepted Answer · 2021-01-25 21:56:11Z

3

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)

edited Jan 25, 2021 at 21:56

answered Jan 25, 2021 at 15:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yannb Over a year ago

Yes .name does not make sense, copy paste issue. I tried to use the coalesce : SELECT type, subtype FROM my_table ORDER BY COALESCE(subtype, type) But it did not work, I end up having all the rows with empty subtypes first, and after that the other rows are correctly sorted by subtypes.

Yannb Over a year ago

Thank you, it works with the empty string comparison !

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related