sqlitecasesql-order-bysql-null

How to SORT with Null and YEAR?

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?

enter image description here

Solution

  • 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.