Simulate NULLS FIRST and NULLS LAST in the ORDER BY Clause

Jim_Knicely · March 2019

When your query contains the ORDER BY clause to sort the result set, alphanumeric NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC), while integer NULL data does the opposite.

Example:

dbadmin=> \d test
                                   List of Fields by Tables
Schema | Table | Column |    Type    | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------------+------+---------+----------+-------------+-------------
public | test  | c1     | int        |    8 |         | f        | f           |
public | test  | c2     | varchar(1) |    1 |         | f        | f           |
(2 rows)

dbadmin=> SELECT * FROM test ORDER BY c2 ASC;
c1 | c2
----+----
  1 | A
  2 | B
  3 | C
  6 | F
    | G
    | H
  4 |
  5 |
(8 rows)

dbadmin=> SELECT * FROM test ORDER BY c2 DESC;
c1 | c2
----+----
  4 |
  5 |
    | H
    | G
  6 | F
  3 | C
  2 | B
  1 | A
(8 rows)

I want the NULL values in the alphanumeric C2 column to appear last in my descending order by query. If you have an Oracle background you know that you can accomplish that using the NULLS LAST feature of the ORDER BY clause. Unfortunately, that doesn’t work in Vertica.

dbadmin=>  SELECT c1, c2 FROM test ORDER BY c2 DESC NULLS LAST;
ERROR 4856:  Syntax error at or near "NULLS" at character 42
LINE 1: SELECT c1, c2 FROM test ORDER BY c2 DESC NULLS LAST;
                                                 ^

But I can simulate this feature in Vertica using the NVL function in the ORDER BY clause!

dbadmin=> SELECT c1, c2 FROM test ORDER BY NVL(c2, '!') DESC;
c1 | c2
----+----
    | H
    | G
  6 | F
  3 | C
  2 | B
  1 | A
  4 |
  5 |
(8 rows)

Similarly, I can do the same with the Integer C1 column:

dbadmin=>  SELECT c1, c2 FROM test ORDER BY c1;
c1 | c2
----+----
    | G
    | H
  1 | A
  2 | B
  3 | C
  4 |
  5 |
  6 | F
(8 rows)

dbadmin=>  SELECT c1, c2 FROM test ORDER BY NVL(c1, 10);
c1 | c2
----+----
  1 | A
  2 | B
  3 | C
  4 |
  5 |
  6 | F
    | G
    | H
(8 rows)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/SELECT/ORDERBYClause.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Null/NVL.htm

Have fun!