Is this the correct syntax for SQLite for making n

Question

select * from table1
order by case Language when null then 1 else 0 end, Language

No matter which way I play around with it, it always displays null values first. Is there a standard way to allow non null values to take ordering precedence?

Thanks guys

Answer 1

You don't need WHEN:

SELECT * FROM table1
ORDER BY Language IS NULL, Language

Operator IS will return 1 on true, 0 otherwise (Operators).

EDIT: Dealing with empty TEXT, also:

SELECT * FROM table1
ORDER BY Language IS NULL OR Language='', Language

ORDER BY clause uses two fields:

1. Language IS NULL OR Language=''. That's a boolean expression (resulting in 0 (false) or 1 (true) in SQLite), same as (Language IS NULL) OR (Language='')

2. When first field has same results, second fiels is used: Language.

This way, whenever Language is NULL or empty TEXT, first field will be 1, relying those results after other results, evaluated to 0. Then, second field is used to sort all results which Language has content.

Answer 2

This is an extension of LS_dev's answer to UNIONs. Not a nice way, but I can't figure out any other way to make it work, since the documentation says:

if the SELECT is a compound SELECT, then ORDER BY expressions that are not aliases to output columns must be exactly the same as an expression used as an output column.

select * from (
    SELECT * FROM table1 -- but this select can have union in it
) ORDER BY Language IS NULL or Language = '', Language

Answer 3

You have to use the is operator when checking for null

select * from table1
order by case when Language is null then 1 else 0 end, 
         Language