ORDER BY

Overview

The ORDER BY clause is used to sort rows of the result received from a SELECT statement, which retrieves records from one or more tables.

Syntax

The following illustrates the syntax of the ORDER BY clause:

SELECT columns
FROM table_name
ORDER BY sort_expression1 [ASC | DESC];

Parameters

• columns: columns that you wish to retrieve
• table_name: table that you want to retrieve records from.
• ORDER BY: expression used to order the results
• ASC or DESC: optional parameter to specify the order in which the results should be returned, either ascending or descending. Default is set to ASC

Examples

We will use the table called salaryemp as an example. In order to create the table, please run the query below:

CREATE TABLE salaryemp  
(  
    emp_id int,
    emp_name string,   
    emp_div string,   
    emp_sal int
);  

INSERT INTO salaryemp   
VALUES
(1002, 'Mike', 'Marketing', 6000),  
(1003, 'Sean', 'Marketing', 6500),  
(1004, 'Victor', 'Finance', 7000),  
(1005, 'Lewis', 'Sales', 5500),  
(1006, 'David', 'Marketing', 8000),
(1007, 'Omar', 'Finance', 8000),
(1008, 'Meghan', 'Finance', 7500),  
(1009, 'Harry', 'Operations', 4500),  
(1010, 'Steve', 'Marketing', 6800),   
(1011, 'David', 'Sales', 8200);

To verify that the values have been inserted successfully, retrieve the results by executing the following code:

SELECT * FROM salaryemp;

+-----------+------------+----------------+-------------+
| emp_id    | emp_name   | emp_div        | emp_sal     |
+-----------+------------+----------------+-------------+
| 1002      | Mike       | Marketing      | 6000        | 
| 1003      | Sean       | Marketing      | 6500        |
| 1004      | Victor     | Finance        | 7000        |
| 1005      | Lewis      | Sales          | 5500        |
| 1006      | David      | Marketing      | 8000        |
| 1007      | Meghan     | Finance        | 7500        |
| 1008      | Harry      | Operations     | 4500        |
| 1009      | Steve      | Marketing      | 6800        |
| 1010      | Omar       | Finance        | 8000        |
| 1011      | David      | Sales          | 8200        |
+-----------+------------+----------------+-------------+

Using ORDER BY in ascending order

This example uses the ORDER BY clause to sort employees by their division:

SELECT emp_name, emp_div
FROM salaryemp
ORDER BY emp_div;

The above query will provide you with the following output:

+------------+----------------+
| emp_name   | emp_div        |
+------------+----------------+
| Victor     | Finance        |
| Omar       | Finance        |
| Meghan     | Finance        |
| Mike       | Marketing      |
| Sean       | Marketing      |
| David      | Marketing      |
| Steve      | Marketing      |
| Harry      | Operations     |
| Lewis      | Sales          |
| David      | Sales          |
+------------+----------------+

Using ORDER BY in descending order

The following statement selects the employee name and employee salary from the salaryemp table and sorts the records in the emp_sal column in descending order:

SELECT * FROM salaryemp
ORDER BY emp_sal DESC;

The result of the query is as follows:

+-----------+------------+----------------+-------------+
| emp_id    | emp_name   | emp_div        | emp_sal     |
+-----------+------------+----------------+-------------+
| 1011      | David      | Sales          | 8200        |
| 1006      | David      | Marketing      | 8000        |
| 1010      | Omar       | Finance        | 8000        |
| 1007      | Meghan     | Finance        | 7500        |
| 1004      | Victor     | Finance        | 7000        |
| 1009      | Steve      | Marketing      | 6800        |
| 1003      | Sean       | Marketing      | 6500        |
| 1002      | Mike       | Marketing      | 6000        | 
| 1005      | Lewis      | Sales          | 5500        |
| 1008      | Harry      | Operations     | 4500        |
+-----------+------------+----------------+-------------+

Using ORDER BY with both ASC & DESC parameters

The following statement selects all records from the salaryemp table and sorts the rows by employee salary in ascending order and employee division in descending order:

SELECT * FROM salaryemp
ORDER BY emp_sal ASC, emp_div DESC;

After implementing the above command, we will get the following output:

+-----------+------------+----------------+-------------+
| emp_id    | emp_name   | emp_div        | emp_sal     |
+-----------+------------+----------------+-------------+
| 1009      | Harry      | Operations     | 4500        |
| 1005      | Lewis      | Sales          | 5500        |
| 1002      | Mike       | Marketing      | 6000        |
| 1003      | Sean       | Marketing      | 6500        |
| 1009      | Steve      | Marketing      | 6800        |
| 1004      | Victor     | Finance        | 7000        |
| 1007      | Meghan     | Finance        | 7500        |
| 1006      | David      | Marketing      | 8000        |
| 1010      | Omar       | Finance        | 8000        |
| 1011      | David      | Sales          | 8200        |
+-----------+------------+----------------+-------------+

Using ORDER BY with STRING or TEXT data types

In this example we are going to create to small tables with above mentioned data types:

CREATE TABLE strings  
(  
    column1 STRING
);  

INSERT INTO strings   
VALUES ('A'), ('B'), ('a'), ('b');

CREATE TABLE texts  
(  
    column1 TEXT
);  

INSERT INTO texts   
VALUES ('A'), ('B'), ('a'), ('b');

When using the ORDER BY clause with these types of data, records with uppercase letters will be sorted lexicographically first, followed by records with lowercase letters.

SELECT * FROM strings ORDER BY column1;
SELECT * FROM texts ORDER BY column1;

 column1 
---------
 A
 B
 a
 b

Using ORDER BY with INTERVAL data type

For this example, we’ll create a new table called interval_data:

CREATE TABLE interval_data (
    duration INTERVAL
);

INSERT INTO interval_data (duration)
VALUES 
    (INTERVAL '1 month 30 days 20 hours'),
    (INTERVAL '2 months 20 hours'),
    (INTERVAL '1 month 30 days 19 hours'),
    (INTERVAL '2 months 1 hours');

ORDER BY on INTERVAL column will sort the values by their leading most significant time unit. In this case months. First are all 1 month values, then all 2 months values.

SELECT * FROM interval_data ORDER BY duration;

        duration        
------------------------
 1 mon 30 days 19:00:00
 1 mon 30 days 20:00:00
 2 mons 01:00:00
 2 mons 20:00:00

It works the same for other time units, such as hours and days.

INSERT INTO interval_data (duration)
VALUES 
    (INTERVAL '24 hours 5 minutes'),
    (INTERVAL '1 day 5 minutes'),
    (INTERVAL '1 day 2 minutes');

SELECT * FROM interval_data ORDER BY duration;

        duration        
------------------------
 24:05:00
 1 day 00:02:00
 1 day 00:05:00
 1 mon 30 days 19:00:00
 1 mon 30 days 20:00:00
 2 mons 01:00:00
 2 mons 20:00:00