Geek asks and answers

Need a postgresql query returning null values ​​after a nonzero value

A postgresql query is required that returns null at the end of a non-zero value, regardless of the order of the upstream / downstream.

For example: - I have a column say purchase_price that has values ​​1, 5, 3, 0, null, null

Then for ORDER BY purchase_price ASChe must return

0
1
3
5
null
null

and for ORDER BY purchase_price DESCHe must return

5
3
1
0
null
null

I need to synthesize a solution that I can apply for the types 'boolean', 'float', 'string', 'integer', 'date'Datatype

+3
source share
3 answers

order by nulls first/last. asc nulls last desc nulls first. :

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

: , , btree. PostgreSQL 9.0 order by col desc nulls last ( , ). , ​​(8.4, ); , ). . PostgreSQL.

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

, , , UNION ALL ( ) , .

+2

I always did this with the help of a vile union, in 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 obviously need to change it to order desc - by setting the values ​​in reverse order, for example ...

Pretty general, but not very elegant.

+1
source

More articles:


All Articles