
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/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/SELECT/ORDERBYClause.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Null/NVL.htm
Have fun!