Is this the correct syntax for SQLite for making nulls appear last?

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

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