ORDER BY Clause

Sorts a query result set on one or more columns.

Syntax

ORDER BY expression [ ASC | DESC ] [,…]

Parameters

expression

One of the following:

  • Name or ordinal number of a SELECT list item. Ordinal numbers are invalid for an ORDER BY clause of an analytic function's OVER clause.
  • Arbitrary expression formed from columns that do not appear in the SELECT list
  • CASE expression

Notes

  • 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 BY clause 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).

Examples

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;