Impala - Order By Clause

The Impala ORDER BY clause is used to sort the data in an ascending or descending order, based on one or more columns. Some databases sort the query results in ascending order by default.

Syntax

Following is the syntax of the ORDER BY clause.

select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]

You can arrange the data in the table in ascending or descending order using the keywords ASC or DESC respectively.

In the same way, if we use NULLS FIRST, all the null values in the table are arranged in the top rows; and if we use NULLS LAST, the rows containing null values will be arranged last.

Example

Assume we have a table named customers in the database my_db and its contents are as follows −

[quickstart.cloudera:21000] > select * from customers;
Query: select * from customers 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 1  | Ramesh   |  32 | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.51s

Following is an example of arranging the data in the customers table, in ascending order of their id’s using the order by clause.

[quickstart.cloudera:21000] > Select * from customers ORDER BY id asc;

On executing, the above query produces the following output.

Query: select * from customers ORDER BY id asc 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.56s

In the same way, you can arrange the data of customers table in descending order using the order by clause as shown below.

[quickstart.cloudera:21000] > Select * from customers ORDER BY id desc;

On executing, the above query produces the following output.

Query: select * from customers ORDER BY id desc 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 6  | Komal    | 22  | MP        | 32000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  |
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.54s