ORDER BY¶

Syntax¶

SELECT ...
FROM ...
ORDER BY orderItem [ , orderItem , ... ]
[ ... ]

Where:

orderItem ::= { <column_alias> | <position> | <expr> } [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ]

Parameters¶

column_alias

Column alias appearing in the query block’s SELECT list.

position

Position of an expression in the SELECT list.

expr

Any expression on tables in the current scope.

{ ASC | DESC }

Optionally returns the values of the sort key in ascending (lowest to highest) or descending (highest to lowest) order.

Default: ASC

NULLS { FIRST | LAST }

Optionally specifies whether NULL values are returned before/after non-NULL values, based on the sort order (ASC or DESC).

Default: Depends on the sort order (ASC or DESC); see the usage notes below for details

Usage notes¶

• All data is sorted according to the numeric byte value of each character in the ASCII table. UTF-8 encoding is supported.

• For numeric values, leading zeros before the decimal point and trailing zeros (0) after the decimal point have no effect on sort order.

• When NULLS FIRST or NULLS LAST isn’t specified, the ordering of NULL values depends on the setting of the DEFAULT_NULL_ORDERING parameter and the sort order:

  • When the sort order is ASC (the default) and the DEFAULT_NULL_ORDERING parameter is set to LAST (the default), NULL values are returned last. Therefore, unless specified otherwise, NULL values are considered to be higher than any non-NULL values.

  • When the sort order is ASC and the DEFAULT_NULL_ORDERING parameter is set to FIRST, NULL values are returned first.

  • When the sort order is DESC and the DEFAULT_NULL_ORDERING parameter is set to FIRST, NULL values are returned last.

  • When the sort order is DESC and the DEFAULT_NULL_ORDERING parameter is set to LAST, NULL values are returned first.

• The sort order isn’t guaranteed to be consistent for values of different data types in semi-structured data, such as an array that contains elements of different data types.

• Top-K pruning can improve the performance of queries that include both LIMIT and ORDER BY clauses. For more information, see Top-K pruning for improved query performance.

• An ORDER BY can be used at different levels in a query, for example in a subquery or inside an OVER() subclause. An ORDER BY inside a subquery or subclause applies only within that subquery or subclause. For example, the ORDER BY in the following query orders results only within the subquery, not the outermost level of the query:

  SELECT * 
    FROM (
      SELECT branch_name
        FROM branch_offices
        ORDER BY monthly_sales DESC
        LIMIT 3
    );
  

  Copy

  In this example, the ORDER BY is specified in the subquery, so the subquery returns the names in order of monthly sales. The ORDER BY in the subquery does not apply to the outer query. This query returns the names of the three branches that had the highest monthly sales, but not necessarily in order by monthly sales.

  Sorting can be expensive. If you want the results of the outer query sorted, use an ORDER BY clause only at the top level of the query, and avoid using ORDER BY clauses in subqueries unless necessary.

Examples¶

Sort order example for strings:

SELECT column1
  FROM VALUES ('a'), ('1'), ('B'), (null), ('2'), ('01'), ('05'), (' this'), ('this'), ('this and that'), ('&'), ('%')
  ORDER BY column1;

+---------------+
| COLUMN1       |
|---------------|
|  this         |
| %             |
| &             |
| 01            |
| 05            |
| 1             |
| 2             |
| B             |
| a             |
| this          |
| this and that |
| NULL          |
+---------------+

Sort order example for numbers:

SELECT column1
  FROM VALUES (3), (4), (null), (1), (2), (6), (5), (0005), (.05), (.5), (.5000)
  ORDER BY column1;

+---------+
| COLUMN1 |
|---------|
|    0.05 |
|    0.50 |
|    0.50 |
|    1.00 |
|    2.00 |
|    3.00 |
|    4.00 |
|    5.00 |
|    5.00 |
|    6.00 |
|    NULL |
+---------+

Sort order examples for NULL values with the DEFAULT_NULL_ORDERING parameter set to LAST (the default):

ALTER SESSION SET DEFAULT_NULL_ORDERING = 'LAST';

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1;

+---------+
| COLUMN1 |
|---------|
|       1 |
|       2 |
|       3 |
|    NULL |
|    NULL |
+---------+

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1 DESC;

+---------+
| COLUMN1 |
|---------|
|    NULL |
|    NULL |
|       3 |
|       2 |
|       1 |
+---------+

This example shows that the NULLS FIRST parameter overrides the DEFAULT_NULL_ORDERING parameter setting:

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1 NULLS FIRST;

+---------+
| COLUMN1 |
|---------|
|    NULL |
|    NULL |
|       1 |
|       2 |
|       3 |
+---------+

Sort order examples for NULL values with the DEFAULT_NULL_ORDERING parameter set to FIRST:

ALTER SESSION SET DEFAULT_NULL_ORDERING = 'FIRST';

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1;

+---------+
| COLUMN1 |
|---------|
|    NULL |
|    NULL |
|       1 |
|       2 |
|       3 |
+---------+

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1 DESC;

+---------+
| COLUMN1 |
|---------|
|       3 |
|       2 |
|       1 |
|    NULL |
|    NULL |
+---------+

This example shows that the NULLS LAST parameter overrides the DEFAULT_NULL_ORDERING parameter setting:

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1 NULLS LAST;

+---------+
| COLUMN1 |
|---------|
|       1 |
|       2 |
|       3 |
|    NULL |
|    NULL |
+---------+