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.

• Unless specified otherwise, NULL values are considered to be higher than any non-NULL values. As a result, the ordering for NULLS depends on the sort order:

  • If the sort order is ASC, NULLS are returned last; to force NULLS to be first, use NULLS FIRST.

  • If the sort order is DESC, NULLS are returned first; to force NULLS to be last, use NULLS LAST.

• The sort order is not 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 example for NULL values:

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 NULLS FIRST;

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

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

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

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

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