Pivotal Knowledge Base

Follow

Sorting (ORDER BY) of data which has NULL values

Environment

Product Version
Pivotal Greenplum 4.3.x

Symptom

After a query has produced an output table (for example, after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. If you choose sorting using ORDER BY and column has NULLs, then NULLs are always last in Pivotal Greenplum Database.

Resolution

As per "SQL 2003 Conformance", Pivotal Greenplum does not support NULLS FIRST/NULLS LAST clause on SELECT statements and sub-queries however you can try with below workaround.

In postgresql, with NULLS FIRST clause

postgres=# select * from test order by t nulls first;
t 
--------

test1
test10
test2
test3
test4
test5
test6
test7
test8
test9
(12 rows)

You can achieve the same results in Pivotal Greenplum using below query

gpadmin=# select * from test order by (t is not null), t;
t 
--------

test1
test10
test2
test3
test4
test5
test6
test7
test8
test9
(12 rows)

Note: Structure of table is as below

gpadmin=# \d test
Table "public.test"
Column | Type | Modifiers 
--------+-------------------+-----------
t | character varying | 
Distributed by: (t)

If you want NULLS LAST:

gpadmin=# select * from test order by (t is not null) desc, t;
t 
--------
test1
test10
test2
test3
test4
test5
test6
test7
test8
test9
(12 rows)

Comments

Powered by Zendesk