CODEWITHSUNDEEP
sqlitenull
Shaun
Shaun

Reputation: 2181

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

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

Upvotes: 7

Views: 3298

Answers (3)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

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.

Upvotes: 23

TWiStErRob
TWiStErRob

Reputation: 46480

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

Upvotes: 1

juergen d
juergen d

Reputation: 204874

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

Upvotes: 5

Related Questions