Sort Null Values

A null value of specified type is defined as the smallest value for its type. Therefore, when sorting in an ascending order, null values always rank first.

x = 1 2 NULL NULL 3;
x;
// output: [1,2,,,3]

sort!(x);
// output: [,,1,2,3]
// sort x in an ascending order with null values on the top

sort!(x,false);
// output: [3,2,1,,]
// sort x in a descending order with null values on the bottom

A negative infinity (-inf) value imported from external source or generated by calculations is smaller than null values.

// use the float function to create a negative inf and compare it with null values
-float("inf")<NULL
// output: true

In SQL statements, null values can be sorted explicitly using NULLS FIRST or NULLS LAST.

sort(3 2 NULL 5, true)
// output: NULL 2 3 5

sort(3 2 NULL 5, false)
// output: 5 3 2 NULL

t = table(1 2 3 4 as id, 3 2 NULL 5 as value)
select * from t order by value asc nulls last

select * from t order by value desc nulls first