How to SORT with Null and YEAR?

Question

i want to sort with SQLITE so my data will show like this:

1. First sort by year asc, then alphabetic
2. If year is null then alphabetic desc

My ideas was :

ORDER by CASE WHEN year = NULL then Name Else year, name

ORDER by YEAR NULLS LAST
   , IIF(year != NULL, name, name desc) ;

But it does not work. I got an error. Do you have some ideas?

Answer 1

You should always compare to NULL with the operator IS and never with =.

SQLite supports the NULLS LAST/FIRST clause since version 3.30.0.
If your version supports it then you can use:

ORDER BY year NULLS LAST,
         CASE WHEN year IS NOT NULL THEN name END,
         CASE WHEN year IS NULL THEN name END DESC;

If you use an older version you can use a boolean expression:

ORDER BY year IS NULL,
         year,
         CASE WHEN year IS NOT NULL THEN name END,
         CASE WHEN year IS NULL THEN name END DESC;

or a CASE expression:

ORDER BY CASE WHEN year IS NOT NULL THEN 1 ELSE 2 END,
         year,
         CASE WHEN year IS NOT NULL THEN name END,
         CASE WHEN year IS NULL THEN name END DESC;

See the demo.