ORDER BY clause
Applies to: Databricks SQL 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: Databricks SQL Databricks Runtime 12.2 LTS and above
A shorthand equivalent to specifying all expressions in the
SELECT
list in the order they occur. Ifsort_direction
ornulls_sort_order
are specified they apply to each expression.-
An expression of any type used to establish an order in which results are returned.
If the expression a literal INT value it is interpreted as a column position in the select list.
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 isASC
and NULLS sort last if sort order isDESC
.NULLS FIRST
: NULL values are returned first regardless of the sort order.NULLS LAST
: NULL values are returned last regardless of the sort order.
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.
Examples
> 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