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, VARCHAR, ARRAY, and SET, 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).

  • When ordering by a collection column, you cannot use DISTINCT.

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;

The following example uses an array in the ORDER BY clause.

=> CREATE TABLE employees (id INT, department VARCHAR(50), grants ARRAY[VARCHAR], grant_values ARRAY[INT]);

=> COPY employees FROM STDIN;
42|Physics|[US-7376,DARPA-1567]|[65000,135000]
36|Physics|[US-7376,DARPA-1567]|[10000,25000]
33|Physics|[US-7376]|[30000]
36|Astronomy|[US-7376,DARPA-1567]|[5000,4000]
\.

=>SELECT * FROM employees ORDER BY grant_values;
 id | department |          grants          |  grant_values
----+------------+--------------------------+----------------
 36 | Astronomy  | ["US-7376","DARPA-1567"] | [5000,4000]
 36 | Physics    | ["US-7376","DARPA-1567"] | [10000,25000]
 33 | Physics    | ["US-7376"]              | [30000]
 42 | Physics    | ["US-7376","DARPA-1567"] | [65000,135000]
(4 rows)