ORDER BY

13min
In this article, you will learn how to sort the result set returned using the ORDER BY clause.
OverviewThe ORDER BY clause is used to sort the rows of the result set from a SELECT statement.
The SELECT statement is used to retrieve records from one or more tables.
SyntaxThe following illustrates the syntax of the ORDER BY clause:
Syntax
|
SELECT columns
FROM table_name
ORDER BY sort_expression1 [ASC | DESC];
SELECT columns
FROM table_name
ORDER BY sort_expression1 [ASC | DESC];
﻿
The parameters and arguments from the syntax are explained below:
columns: The columns that you wish to retrieve.
table_name: The tables that you want to retrieve records from. 
ORDER BY: It is an expression used to order the results as you wish to return. The expression could be ascending (ASC) or descending (DESC). 
ExamplesWe will use the table called salaryemp for an example. Run the query below to create the table:
Create a table
|
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);
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 result set using the command below: 
Display the table
|
SELECT * FROM salaryemp;
SELECT * FROM salaryemp;
﻿
Output
|
+-----------+------------+----------------+-------------+
| 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        |
+-----------+------------+----------------+-------------+
+-----------+------------+----------------+-------------+
| 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        |
+-----------+------------+----------------+-------------+
﻿
Case #1: Using ORDER BY in ascending orderThis example uses the ORDER BY clause to sort employees by their division:
Order by query
|
SELECT emp_name, emp_div
FROM salaryemp
ORDER BY emp_div;
SELECT emp_name, emp_div
FROM salaryemp
ORDER BY emp_div;
﻿
The ASC option is the default expression of ORDER BY, you can omit it in the query.
The above query will give the following result:
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          |
+------------+----------------+
+------------+----------------+
| emp_name   | emp_div        |
+------------+----------------+
| Victor     | Finance        |
| Omar       | Finance        |
| Meghan     | Finance        |
| Mike       | Marketing      |
| Sean       | Marketing      |
| David      | Marketing      |
| Steve      | Marketing      |
| Harry      | Operations     |
| Lewis      | Sales          |
| David      | Sales          |
+------------+----------------+
﻿
Case #2: Using ORDER BY in descending orderThe 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:
Display the table
|
SELECT * FROM salaryemp
ORDER BY emp_sal DESC;
SELECT * FROM salaryemp
ORDER BY emp_sal DESC;
﻿
The result of the query is as follows:
Output
|
+-----------+------------+----------------+-------------+
| 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        |
+-----------+------------+----------------+-------------+
+-----------+------------+----------------+-------------+
| 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        |
+-----------+------------+----------------+-------------+
﻿
Case #3: Using ORDER BY with both ASC & DESCThe 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:
Order by query
|
SELECT * FROM salaryemp
ORDER BY emp_sal ASC, emp_div DESC;
SELECT * FROM salaryemp
ORDER BY emp_sal ASC, emp_div DESC;
﻿
After implementing the above command, we will get the below output:
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        |
+-----------+------------+----------------+-------------+
+-----------+------------+----------------+-------------+
| 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        |
+-----------+------------+----------------+-------------+
﻿
﻿
Updated 11 May 2023
Did this page help you?
Yes
HAVING
LIMIT
Docs powered by archbee
TABLE OF CONTENTS
Overview
Syntax
Examples
Case #1: Using ORDER BY in ascending order
Case #2: Using ORDER BY in descending order
Case #3: Using ORDER BY with both ASC & DESC