Sorting (By Order) of Data which has NULL Values

Products

• VMware Tanzu Greenplum

Issue/Introduction

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.

Environment

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)

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) 4.3.x