ORDER BY Clause
Sorts a query result set on one or more columns.
ORDER BY expression [ ASC | DESC ] [,…]
One of the following:
- The ordinal number refers to the position of the result column, counting from the left beginning at one. This makes it possible to order by a column that does not have a unique name. (You can assign a name to a result column using the AS clause.)
- While the user's current locale and collation sequence are used to compare strings and determine the results of the
ORDER BYclause of a query, Vertica projection data is always stored sorted by the ASCII (binary) collating sequence.
- For INTEGER, INT, and DATE/TIME data types, NULL appears first (smallest) in ascending order.
- For FLOAT, BOOLEAN, CHAR, and VARCHAR, NULL appears last (largest) in ascending order.
The ORDER BY clause may contain only columns or expressions that are in the window partition clause (see Window Partition Clause).
The follow example returns all the city and deal size for customer Metamedia, sorted by deal size in descending order.
=> SELECT customer_city, deal_siz FROM customer_dimension WHERE customer_name = 'Metamedia' ORDER BY deal_size DESC; customer_city | deal_size ------------------+----------- El Monte | 4479561 Athens | 3815416 Ventura | 3792937 Peoria | 3227765 Arvada | 2671849 Coral Springs | 2643674 Fontana | 2374465 Rancho Cucamonga | 2214002 Wichita Falls | 2117962 Beaumont | 1898295 Arvada | 1321897 Waco | 1026854 Joliet | 945404 Hartford | 445795 (14 rows)
The following example uses a transform function. It returns an error because the ORDER BY column is not in the window partition.
=> CREATE TABLE t(geom geometry(200), geog geography(200)); => SELECT PolygonPoint(geom) OVER(PARTITION BY geom) AS SEL_0 FROM t ORDER BY geog; ERROR 2521: Cannot specify anything other than user defined transforms and partitioning expressions in the ORDER BY list
The following example, using the same table, corrects this error.
=> SELECT PolygonPoint(geom) OVER(PARTITION BY geom) AS SEL_0 FROM t ORDER BY geom;