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 text,
emp_div text,
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:
+-----------+------------+----------------+-------------+
| 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 TEXT
data types
In this example we are going to create to small tables with above mentioned data types:
CREATE TABLE strings
(
column1 text
);
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