SQL Order By Keyword
In this lesson, you will learn how to sort selected records using SQL.
The ORDER BY Keyword In SQL
The ORDER BY keyword is used in a SELECT statement to sort the retrieved records in ascending or descending order.
Syntax
When sorting the selected records, you can specify one or more columns to sort the records based on their values.
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
When sorting records in ascending order, it's not required to add ASC at the end because it's the default value. However, to sort records in descending order you have to add DESC at the end of the statement.
Preparing A Database
Since you will learn how to sort the selected records, you must have a database table with some records to practice sorting them. Execute the following query to create the database.
Example
-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;
-- Create a new database called "demo"
CREATE DATABASE demo;
-- Select the "demo" database
USE demo;
-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
is_married BOOLEAN,
salary DECIMAL(7,2),
birthday Date
);
-- Insert 10 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "Johnson", false, 800, "1990-10-23");
INSERT INTO employees VALUES (null, "Benjamin", "Anderson", false, 750, "1992-05-08");
INSERT INTO employees VALUES (null, "Olivia", "Smith", true, 900, "1988-07-12");
INSERT INTO employees VALUES (null, "William", "Davis", false, 845, "1994-12-04");
INSERT INTO employees VALUES (null, "Sophia", "Brown", true, 1000, "1991-10-27");
INSERT INTO employees VALUES (null, "Daniel", "Martinez", false, 700, "1993-11-12");
INSERT INTO employees VALUES (null, "Ava", "Taylor", true, 850, "1995-03-10");
INSERT INTO employees VALUES (null, "James", "Wilson", true, 920, "1990-02-14");
INSERT INTO employees VALUES (null, "Mia", "Garcia", false, 780, "1991-08-12");
INSERT INTO employees VALUES (null, "Ethan", "Jones", true, 880, "1985-09-20");
Order By Single Column
The following query sorts all employees in ascending order based on their salaries. That means the employee with the lowest salary will be shown first.
Example 1
SELECT * FROM employees
ORDER BY salary;
Same query can be written as follows.
SELECT * FROM employees
ORDER BY salary ASC;
Output
| id | first_name | last_name | is_married | salary | birthday |
|---|---|---|---|---|---|
| 6 | Daniel | Martinez | 0 | 700.00 | 1993-11-12 |
| 2 | Benjamin | Anderson | 0 | 750.00 | 1992-05-08 |
| 9 | Mia | Garcia | 0 | 780.00 | 1991-08-12 |
| 1 | Emily | Johnson | 0 | 800.00 | 1990-10-23 |
| 4 | William | Davis | 0 | 845.00 | 1994-12-04 |
| 7 | Ava | Taylor | 1 | 850.00 | 1995-03-10 |
| 10 | Ethan | Jones | 1 | 880.00 | 1985-09-20 |
| 3 | Olivia | Smith | 1 | 900.00 | 1988-07-12 |
| 8 | James | Wilson | 1 | 920.00 | 1990-02-14 |
| 5 | Sophia | Brown | 1 | 1000.00 | 1991-10-27 |
The following query sorts all employees in descending order based on their salaries. That means the employee with the highest salary will be shown first.
Example 2
SELECT * FROM employees
ORDER BY salary DESC;
Output
| id | first_name | last_name | is_married | salary | birthday |
|---|---|---|---|---|---|
| 5 | Sophia | Brown | 1 | 1000.00 | 1991-10-27 |
| 8 | James | Wilson | 1 | 920.00 | 1990-02-14 |
| 3 | Olivia | Smith | 1 | 900.00 | 1988-07-12 |
| 10 | Ethan | Jones | 1 | 880.00 | 1985-09-20 |
| 7 | Ava | Taylor | 1 | 850.00 | 1995-03-10 |
| 4 | William | Davis | 0 | 845.00 | 1994-12-04 |
| 1 | Emily | Johnson | 0 | 800.00 | 1990-10-23 |
| 9 | Mia | Garcia | 0 | 780.00 | 1991-08-12 |
| 2 | Benjamin | Anderson | 0 | 750.00 | 1992-05-08 |
| 6 | Daniel | Martinez | 0 | 700.00 | 1993-11-12 |
The following query sorts all employees in ascending order based on their birthdays. That means employees with older age will appear first.
Example 3
SELECT * FROM employees
ORDER BY salary;
Same query can be written as follows.
SELECT * FROM employees
ORDER BY birthday;
Output
| id | first_name | last_name | is_married | salary | birthday |
|---|---|---|---|---|---|
| 10 | Ethan | Jones | 1 | 880.00 | 1985-09-20 |
| 3 | Olivia | Smith | 1 | 900.00 | 1988-07-12 |
| 8 | James | Wilson | 1 | 920.00 | 1990-02-14 |
| 1 | Emily | Johnson | 0 | 800.00 | 1990-10-23 |
| 9 | Mia | Garcia | 0 | 780.00 | 1991-08-12 |
| 5 | Sophia | Brown | 1 | 1000.00 | 1991-10-27 |
| 2 | Benjamin | Anderson | 0 | 750.00 | 1992-05-08 |
| 6 | Daniel | Martinez | 0 | 700.00 | 1993-11-12 |
| 4 | William | Davis | 0 | 845.00 | 1994-12-04 |
| 7 | Ava | Taylor | 1 | 850.00 | 1995-03-10 |
Order By Multiple Column
The following query sorts all employees in ascending order based on their first name, and then based on their last name.
Example 1
SELECT * FROM employees
ORDER BY first_name, last_name;
Same query can be written as follows.
SELECT * FROM employees
ORDER BY first_name ASC, last_name ASC;
Output
| id | first_name | last_name | is_married | salary | birthday |
|---|---|---|---|---|---|
| 10 | Ethan | Jones | 1 | 880.00 | 1985-09-20 |
| 3 | Olivia | Smith | 1 | 900.00 | 1988-07-12 |
| 8 | James | Wilson | 1 | 920.00 | 1990-02-14 |
| 1 | Emily | Johnson | 0 | 800.00 | 1990-10-23 |
| 9 | Mia | Garcia | 0 | 780.00 | 1991-08-12 |
| 5 | Sophia | Brown | 1 | 1000.00 | 1991-10-27 |
| 2 | Benjamin | Anderson | 0 | 750.00 | 1992-05-08 |
| 6 | Daniel | Martinez | 0 | 700.00 | 1993-11-12 |
| 4 | William | Davis | 0 | 845.00 | 1994-12-04 |
| 7 | Ava | Taylor | 1 | 850.00 | 1995-03-10 |
The following query sorts all employees in ascending order based on their social status, and then in descending order based on their salary. So single employees with higher salary will be shown first.
Example 2
SELECT * FROM employees
ORDER BY is_married, salary DESC;
Same query can be written as follows.
SELECT * FROM employees
ORDER BY is_married ASC, salary DESC;
Output
| id | first_name | last_name | is_married | salary | birthday |
|---|---|---|---|---|---|
| 4 | William | Davis | 0 | 845.00 | 1994-12-04 |
| 1 | Emily | Johnson | 0 | 800.00 | 1990-10-23 |
| 9 | Mia | Garcia | 0 | 780.00 | 1991-08-12 |
| 2 | Benjamin | Anderson | 0 | 750.00 | 1992-05-08 |
| 6 | Daniel | Martinez | 0 | 700.00 | 1993-11-12 |
| 5 | Sophia | Brown | 1 | 1000.00 | 1991-10-27 |
| 8 | James | Wilson | 1 | 920.00 | 1990-02-14 |
| 3 | Olivia | Smith | 1 | 900.00 | 1988-07-12 |
| 10 | Ethan | Jones | 1 | 880.00 | 1985-09-20 |
| 7 | Ava | Taylor | 1 | 850.00 | 1995-03-10 |
Order By Newly Created Column
The following query sorts all employees in ascending order based on their full name.
Note: we select id, full name and salary fields only.
Example
SELECT
id as 'ID',
CONCAT(first_name, ' ', last_name) AS 'Full Name',
salary AS 'Salary'
FROM employees
ORDER BY CONCAT(first_name, ' ', last_name);
Output
| ID | Full Name | Salary |
|---|---|---|
| 7 | Ava Taylor | 850.00 |
| 2 | Benjamin Anderson | 750.00 |
| 6 | Daniel Martinez | 700.00 |
| 1 | Emily Johnson | 800.00 |
| 10 | Ethan Jones | 880.00 |
| 8 | James Wilson | 920.00 |
| 9 | Mia Garcia | 780.00 |
| 3 | Olivia Smith | 900.00 |
| 5 | Sophia Brown | 1000.00 |
| 4 | William Davis | 845.00 |
Sort Rows Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:
id INT NOT NULL PRIMARY KEY AUTO_INCREMENTfirst_name VARCHAR(50)last_name VARCHAR(50)join_date DATE
3- Write a query that inserts 5 rows in the "customers" table.
4- Write a query that returns all records from the "customers" table sorted in ascending order based on their join date.
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS customers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
join_date DATE
);
INSERT INTO customers (first_name, last_name, join_date) VALUES
('Jack', 'Reed', '2020-02-14'),
('Emma', 'Clark', '2018-05-24'),
('Luke', 'Lee', '2023-10-10'),
('Jim', 'Wilson', '2019-07-16'),
('Lily', 'Brooks', '2022-11-01');
SELECT * FROM customers
ORDER BY join_date;
