ORDER BY Clause
ORDER BY clause of a
SELECT statement sorts the result set
based on the values from one or more columns.
For distributed queries, this is a relatively expensive operation, because the entire result set must be
produced and transferred to one node before the sorting can happen. This can require more memory capacity
than a query without
ORDER BY. Even if the query takes approximately the same time to finish
with or without the
ORDER BY clause, subjectively it can appear slower because no results
are available until all processing is finished, rather than results coming back gradually as rows matching
WHERE clause are found. Therefore, if you only need the first N results from the sorted
result set, also include the
LIMIT clause, which reduces network overhead and the memory
requirement on the coordinator node.
In Impala 1.4.0 and higher, the
LIMIT clause is now optional (rather than required) for
queries that use the
ORDER BY clause. Impala automatically uses a temporary disk work area
to perform the sort if the sort operation would otherwise exceed the Impala memory limit for a particular
The full syntax for the
ORDER BY clause is:
ORDER BY col_ref [, col_ref ...] [ASC | DESC] [NULLS FIRST | NULLS LAST] col_ref ::= column_name | integer_literal
Although the most common usage is
ORDER BY column_name, you can also
ORDER BY 1 to sort by the first column of the result set,
2 to sort by the second column, and so on. The number must be a numeric literal, not some other kind
of constant expression. (If the argument is some other expression, even a
STRING value, the
query succeeds but the order of results is undefined.)
ORDER BY column_number can only be used when the query explicitly lists
the columns in the
SELECT list, not with
SELECT * queries.
Ascending and descending sorts:
The default sort order (the same as using the
ASC keyword) puts the smallest values at the
start of the result set, and the largest values at the end. Specifying the
reverses that order.
Sort order for NULL values:
See NULL for details about how
NULL values are positioned
in the sorted result set, and how to use the
NULLS FIRST and
clauses. (The sort position for
NULL values in
ORDER BY ... DESC queries is
changed in Impala 1.2.1 and higher to be more standards-compliant, and the
NULLS FIRST and
NULLS LAST keywords are new in Impala 1.2.1.)
Prior to Impala 1.4.0, Impala required any query including an
ORDER BY clause to also use a
LIMIT clause. In Impala 1.4.0 and
LIMIT clause is optional for
ORDER BY queries. In cases where
sorting a huge result set requires enough memory to exceed the Impala memory limit for a particular node,
Impala automatically uses a temporary disk work area to perform the sort operation.
Complex type considerations:
In Impala 2.3 and higher, the complex data types
MAP are available. These columns cannot
be referenced directly in the
ORDER BY clause.
When you query a complex type column, you use join notation to "unpack" the elements
of the complex type, and within the join query you can include an
clause to control the order in the result set of the scalar elements from the complex type.
See Complex Types (Impala 2.3 or higher only) for details about Impala support for complex types.
The following query shows how a complex type column cannot be directly used in an
ORDER BY clause:
CREATE TABLE games (id BIGINT, score ARRAY <BIGINT>) STORED AS PARQUET; ...use LOAD DATA to load externally created Parquet files into the table... SELECT id FROM games ORDER BY score DESC; ERROR: AnalysisException: ORDER BY expression 'score' with complex type 'ARRAY<BIGINT>' is not supported.
The following query retrieves the user ID and score, only for scores greater than one million,
with the highest scores for each user listed first.
Because the individual array elements are now represented as separate rows in the result set,
they can be used in the
ORDER BY clause, referenced using the
pseudocolumn that represents each array element.
SELECT id, item FROM games, games.score WHERE item > 1000000 ORDER BY id, item desc;
The following queries use similar
ORDER BY techniques with variations of the
table, where the complex type is an
elements to represent additional details about each game that was played.
For an array of structures, the fields of the structure are referenced as
For an array of maps, the keys and values within each array element are referenced as
CREATE TABLE games2 (id BIGINT, play array < struct <game_name: string, score: BIGINT, high_score: boolean> >) STORED AS PARQUET ...use LOAD DATA to load externally created Parquet files into the table... SELECT id, item.game_name, item.score FROM games2, games2.play WHERE item.score > 1000000 ORDER BY id, item.score DESC; CREATE TABLE games3 (id BIGINT, play ARRAY < MAP <STRING, BIGINT> >) STORED AS PARQUET; ...use LOAD DATA to load externally created Parquet files into the table... SELECT id, info.key AS k, info.value AS v from games3, games3.play AS plays, games3.play.item AS info WHERE info.KEY = 'score' AND info.VALUE > 1000000 ORDER BY id, info.value desc;
LIMIT clause is now optional on
ORDER BY queries, if your
query only needs some number of rows that you can predict in advance, use the
to reduce unnecessary processing. For example, if the query has a clause
LIMIT 10, each data
node sorts its portion of the relevant result set and only returns 10 rows to the coordinator node. The
coordinator node picks the 10 highest or lowest row values out of this small intermediate result set.
ORDER BY clause is applied to an early phase of query processing, such as a subquery
or a view definition, Impala ignores the
ORDER BY clause. To get ordered results from a
subquery or view, apply an
ORDER BY clause to the outermost or final
ORDER BY is often used in combination with
LIMIT to perform "top-N"
SELECT user_id AS "Top 10 Visitors", SUM(page_views) FROM web_stats GROUP BY page_views, user_id ORDER BY SUM(page_views) DESC LIMIT 10;
ORDER BY is sometimes used in combination with
LIMIT to paginate query results, although it is relatively inefficient to issue multiple
queries like this against the large tables typically used with Impala:
SELECT page_title AS "Page 1 of search results", page_url FROM search_content WHERE LOWER(page_title) LIKE '%game%') ORDER BY page_title LIMIT 10 OFFSET 0; SELECT page_title AS "Page 2 of search results", page_url FROM search_content WHERE LOWER(page_title) LIKE '%game%') ORDER BY page_title LIMIT 10 OFFSET 10; SELECT page_title AS "Page 3 of search results", page_url FROM search_content WHERE LOWER(page_title) LIKE '%game%') ORDER BY page_title LIMIT 10 OFFSET 20;
Impala sorts the intermediate results of an
ORDER BY clause in memory whenever practical. In
a cluster of N DataNodes, each node sorts roughly 1/Nth of the result set, the exact proportion varying
depending on how the data matching the query is distributed in HDFS.
If the size of the sorted intermediate result set on any DataNode would cause the query to exceed the Impala memory limit, Impala sorts as much as practical in memory, then writes partially sorted data to disk. (This technique is known in industry terminology as "external sorting" and "spilling to disk".) As each 8 MB batch of data is written to disk, Impala frees the corresponding memory to sort a new 8 MB batch of data. When all the data has been processed, a final merge sort operation is performed to correctly order the in-memory and on-disk results as the result set is transmitted back to the coordinator node. When external sorting becomes necessary, Impala requires approximately 60 MB of RAM at a minimum for the buffers needed to read, write, and sort the intermediate results. If more RAM is available on the DataNode, Impala will use the additional RAM to minimize the amount of disk I/O for sorting.
This external sort technique is used as appropriate on each DataNode (possibly including the coordinator node) to sort the portion of the result set that is processed on that node. When the sorted intermediate results are sent back to the coordinator node to produce the final result set, the coordinator node uses a merge sort technique to produce a final sorted result set without using any extra resources on the coordinator node.
Configuration for disk usage:
By default, intermediate files used during large sort, join, aggregation, or analytic function operations
are stored in the directory /tmp/impala-scratch . These files are removed when the
operation finishes. (Multiple concurrent queries can perform operations that use the "spill to disk"
technique, without any name conflicts for these temporary files.) You can specify a different location by
starting the impalad daemon with the
--scratch_dirs="path_to_directory" configuration option.
You can specify a single directory, or a comma-separated list of directories. The scratch directories must
be on the local filesystem, not in HDFS. You might specify different directory paths for different hosts,
depending on the capacity and speed
of the available storage devices. In Impala 2.3 or higher, Impala successfully starts (with a warning
Impala successfully starts (with a warning written to the log) if it cannot create or read and write files
in one of the scratch directories. If there is less than 1 GB free on the filesystem where that directory resides,
Impala still runs, but writes a warning message to its log. If Impala encounters an error reading or writing
files in a scratch directory during a query, Impala logs the error and the query fails.
Sorting considerations: Although you can specify an
ORDER BY clause in an
INSERT ... SELECT statement, any
ORDER BY clause is ignored and the
results are not necessarily sorted. An
INSERT ... SELECT operation potentially creates
many different data files, prepared on different data nodes, and therefore the notion of the data being
stored in sorted order is impractical.
ORDER BYclause without an additional
LIMITclause is ignored in any view definition. If you need to sort the entire result set from a view, use an
ORDER BYclause in the
SELECTstatement that queries the view. You can still make a simple "top 10" report by combining the
LIMITclauses in the same view definition:
[localhost:21000] > create table unsorted (x bigint); [localhost:21000] > insert into unsorted values (1), (9), (3), (7), (5), (8), (4), (6), (2); [localhost:21000] > create view sorted_view as select x from unsorted order by x; [localhost:21000] > select x from sorted_view; -- ORDER BY clause in view has no effect. +---+ | x | +---+ | 1 | | 9 | | 3 | | 7 | | 5 | | 8 | | 4 | | 6 | | 2 | +---+ [localhost:21000] > select x from sorted_view order by x; -- View query requires ORDER BY at outermost level. +---+ | x | +---+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | +---+ [localhost:21000] > create view top_3_view as select x from unsorted order by x limit 3; [localhost:21000] > select x from top_3_view; -- ORDER BY and LIMIT together in view definition are preserved. +---+ | x | +---+ | 1 | | 2 | | 3 | +---+
With the lifting of the requirement to include a
LIMIT clause in every
BY query (in Impala 1.4 and higher):
Now the use of scratch disk space raises the possibility of an "out of disk space" error on a particular DataNode, as opposed to the previous possibility of an "out of memory" error. Make sure to keep at least 1 GB free on the filesystem used for temporary sorting work.
In Impala 1.2.1 and higher, all
NULL values come at the end of the result set for
ORDER BY ... ASC queries, and at the beginning of the result set for
ORDER BY ...
DESC queries. In effect,
NULL is considered greater than all other values for
sorting purposes. The original Impala behavior always put
NULL values at the end, even for
ORDER BY ... DESC queries. The new behavior in Impala 1.2.1 makes Impala more compatible
with other popular database systems. In Impala 1.2.1 and higher, you can override or specify the sorting
NULL by adding the clause
NULLS FIRST or
LAST at the end of the
ORDER BY clause.
[localhost:21000] > create table numbers (x int); [localhost:21000] > insert into numbers values (1), (null), (2), (null), (3); [localhost:21000] > select x from numbers order by x nulls first; +------+ | x | +------+ | NULL | | NULL | | 1 | | 2 | | 3 | +------+ [localhost:21000] > select x from numbers order by x desc nulls first; +------+ | x | +------+ | NULL | | NULL | | 3 | | 2 | | 1 | +------+ [localhost:21000] > select x from numbers order by x nulls last; +------+ | x | +------+ | 1 | | 2 | | 3 | | NULL | | NULL | +------+ [localhost:21000] > select x from numbers order by x desc nulls last; +------+ | x | +------+ | 3 | | 2 | | 1 | | NULL | | NULL | +------+
See SELECT Statement for further examples of queries with the
Analytic functions use the
ORDER BY clause in a different context to define the sequence in
which rows are analyzed. See Impala Analytic Functions for details.