Skip to main content
Last updated on

ORDER BY clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the result rows in a sorted manner in the user specified order. Unlike the SORT BY clause, this clause guarantees a total order in the output.

Syntax​

ORDER BY { { ALL [ sort_direction] [ nulls_sort_oder ] } |
           { expression [ sort_direction ] [ nulls_sort_oder ] } [, ...] }

sort_direction
  [ ASC | DESC ]

nulls_sort_order
  [ NULLS FIRST | NULLS LAST ]

Parameters​

  • ALL

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above

    A shorthand equivalent to specifying all expressions in the SELECT list in the order they occur. If sort_direction or nulls_sort_order are specified they apply to each expression.

  • expression

    An expression of any type used to establish an order in which results are returned.

    If the expression is a literal INTEGER value it is interpreted as a column position in the select list.

    If a literal INTEGER value refers to a position that is out of range, Databricks raises ORDER_BY_POS_OUT_OF_RANGE.

  • sort_direction

    Specifies the sort order for the order by expression.

    • ASC: The sort direction for this expression is ascending.
    • DESC: The sort order for this expression is descending.

    If sort direction is not explicitly specified, then by default rows are sorted ascending.

  • nulls_sort_order

    Optionally specifies whether NULL values are returned before/after non-NULL values. If null_sort_order is not specified, then NULLs sort first if sort order is ASC and NULLS sort last if sort order is DESC.

    • NULLS FIRST: NULL values are returned first regardless of the sort order.
    • NULLS LAST: NULL values are returned last regardless of the sort order.

If the expression has a data type that does not support ordering, such as MAP, Databricks raises DATATYPE_MISMATCH.INVALID_ORDERING_TYPE.

When specifying more than one expression sorting occurs left to right. All rows are sorted by the first expression. If there are duplicate values for the first expression the second expression is used to resolve order within the group of duplicates and so on. The resulting order not deterministic if there are duplicate values across all order by expressions.

Common error conditions​

Examples​

SQL
> CREATE TABLE person (id INT, name STRING, age INT);
> INSERT INTO person VALUES
    (100, 'John' , 30),
    (200, 'Mary' , NULL),
    (300, 'Mike' , 80),
    (400, 'Jerry', NULL),
    (500, 'Dan'  ,  50);

-- Sort rows by age. By default rows are sorted in ascending manner with NULL FIRST.
> SELECT name, age FROM person ORDER BY age;
 Jerry NULL
  Mary NULL
  John   30
   Dan   50
  Mike   80

-- Sort rows in ascending manner keeping null values to be last.
> SELECT name, age FROM person ORDER BY age NULLS LAST;
  John   30
   Dan   50
  Mike   80
  Mary NULL
 Jerry NULL

-- Sort rows by age in descending manner, which defaults to NULL LAST.
> SELECT name, age FROM person ORDER BY age DESC;
  Mike   80
   Dan   50
  John   30
 Jerry NULL
  Mary NULL

-- Sort rows in ascending manner keeping null values to be first.
> SELECT name, age FROM person ORDER BY age DESC NULLS FIRST;
 Jerry NULL
  Mary NULL
  Mike   80
   Dan   50
  John   30

-- Sort rows based on more than one column with each column having different
-- sort direction.
> SELECT * FROM person ORDER BY name ASC, age DESC;
 500    Dan   50
 400  Jerry NULL
 100   John   30
 200   Mary NULL
 300   Mike   80

-- Sort rows based on all columns in the select list
> SELECT * FROM person ORDER BY ALL ASC;
 100   John   30
 200   Mary NULL
 300   Mike   80
 400  Jerry NULL
 500    Dan   50

-- Positional reference out of range.
> SELECT name FROM person ORDER BY 2;
  Error: ORDER_BY_POS_OUT_OF_RANGE

-- Ordering by a type that does not support ordering.
> SELECT map('a', 1) AS m ORDER BY 1;
  Error: DATATYPE_MISMATCH

Related articles​

On this page