CODEWITHSUNDEEP
ruby-on-railspostgresqlsql-order-by
Salil
Salil

Reputation: 47512

Need a postgresql query which return null values afterf the non-null value

Need a postgresql query which return null value at the end of the non-null value irrespective of the Ascending/Descending Order.

For ex:- I have a column say purchase_price having values 1, 5, 3, 0, null, null

Then for ORDER BY purchase_price ASC It should return 

0
1
3
5
null
null

and for ORDER BY purchase_price DESC It should return 

5
3
1
0
null
null

I need generalise solution which i can able to apply for the 'boolean', 'float', 'string', 'integer', 'date' Datatype

Upvotes: 3

Views: 1016

Answers (3)

Seth Robertson
Seth Robertson

Reputation: 31461

select * from (select * from foo where x is not null order by x desc) a union all select  * from foo where x is null;

A cheap hack, but you can select the non-null cases with whatever ordering you want, and then do a UNION ALL (all is all important here) with the null cases, which will strictly follow afterwards.

Upvotes: 2

Denis de Bernardy
Denis de Bernardy

Reputation: 78513

order by supports nulls first/last. By default asc gets nulls last and desc gets nulls first. You can override them for each column:

order by col1 desc nulls last, col2 desc nulls first, etc

An extra note on this: the underlying reason is that nulls they get placed at the end of the btree index. In PostgreSQL 9.0, order by col desc nulls last is optimized to split the index scan in two (reverse scan on not null rows, followed by null rows). This was not the case in the original version where it was added (8.4 if memory serves); the latter proceeds to do a seq scan of the whole table followed by a quicksort). And older versions do not support the feature at all. So be wary of using it in anything but recent versions of PostgreSQL.

Upvotes: 5

Neville Kuyt
Neville Kuyt

Reputation: 29639

I've always done this with a sneaky union, along the following lines:

select *, '1' as orderCol
from table
where nullableColumn is not null
union
select *, '2' as orderCol
from table
where nullableColumn is null
order by orderCol, nullableColumn

You clearly have to change it for ordering desc - by setting the values the other way round, for instance...

Fairly general, but not very elegant.

Upvotes: 1

Related Questions