SQL ORDER BY clause

Last update on October 01 2024 12:45:56 (UTC/GMT +8 hours)

ORDER BY clause

The ORDER BY clause orders or sorts the result of a query according to the values in one or more specific columns. More than one columns can be ordered one within another. It depends on the user that, whether to order them in ascending or descending order. The default order is ascending.

The SQL ORDER BY clause is used with the SQL SELECT statement.

Note: SQL ORDER BY clause always come at the end of a SELECT statement.

Syntax:

SELECT <column_list> FROM < table name >.
WHERE <condition>
ORDER BY <columns> [ASC | DESC];

Parameters:

Name	Description
table_name	Name of the table.
column_list	Name of the columns of the table.
columns	Name of the columns which will participate in ordering.

Syntax diagram: SELECT query

Syntax diagram: SELECT query

Some important questions related to the SQL ORDER BY clause:

What is the purpose of the SQL ORDER BY clause?

The ORDER BY clause is used to sort the result set returned by a SELECT statement based on one or more columns.

What are some common use cases for the ORDER BY clause?

Sorting results for display purposes, arranging data for reports, identifying top or bottom performers, and organizing data for further analysis are some common use cases for the ORDER BY clause.

What is the default sorting order in SQL if no explicit order is specified using ORDER BY?

By default, SQL sorts the result set in ascending order if no explicit sorting order is specified using ORDER BY.

How to sort data in descending order using the ORDER BY clause?

You can specify the DESC keyword after the column name in the ORDER BY clause to sort the data in descending order. For example: ORDER BY column_name DESC.

Is it possible to use the ORDER BY clause without specifying any column?

No, the ORDER BY clause requires specifying one or more columns to sort the result set. Otherwise, it doesn't have any meaning.

Can you sort data based on multiple columns using the ORDER BY clause?

Yes, you can specify multiple columns in the ORDER BY clause to sort the data. The sorting is performed based on the first column specified, and if there are ties, it sorts based on the subsequent columns.

Can you use column aliases in the ORDER BY clause?

Yes, you can use column aliases defined in the SELECT statement in the ORDER BY clause. However, some databases may not allow using column aliases in the ORDER BY clause if they are defined in the same SELECT statement.

In which position of the SELECT statement does the ORDER BY clause come?

The ORDER BY clause typically comes after the WHERE clause and before the LIMIT or OFFSET clauses in a SELECT statement.

Is it possible to sort data based on expressions or functions using the ORDER BY clause?

Yes, you can use expressions or functions in the ORDER BY clause to sort data based on computed values.

What is the difference between the WHERE clause and the ORDER BY clause?

The WHERE clause is used to filter rows based on specified conditions, whereas the ORDER BY clause is used to sort the result set.

Example: SQL ORDER BY clause - Sorting on column names

Find the agent name, working area and commision; sort the result by agent code ascending order.

Sample table: agents

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

To get 'agent_name', 'agent_name','working_area' and 'commission' from the 'agents' table with following condition -

1. 'agent_code' should come in ascending order,

the following SQL statement can be used:


-- This SQL query retrieves specific columns from the 'agents' table and sorts the result set by the 'agent_code' column.
-- SELECT statement begins
SELECT 
    agent_code, -- Select the 'agent_code' column from the 'agents' table
    agent_name, -- Select the 'agent_name' column from the 'agents' table
    working_area, -- Select the 'working_area' column from the 'agents' table
    commission -- Select the 'commission' column from the 'agents' table
FROM 
    agents -- Retrieve data from the 'agents' table
ORDER BY 
    agent_code; -- Sort the result set by the 'agent_code' column in ascending order

Explanation:

This SQL code is a SELECT statement that retrieves specific columns from the 'agents' table.

The columns selected are 'agent_code', 'agent_name', 'working_area', and 'commission'.

The data is retrieved from the 'agents' table.

The result set is sorted in ascending order based on the 'agent_code' column using the ORDER BY clause.

The query does not filter or manipulate the data further; it simply retrieves and sorts the specified columns from the 'agents' table.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

Visual Presentation: SQL ORDER BY ascending - descending

SQL ORDER BY ascending, descending

Example: SQL - Usage of the ORDER BY clause with the DESC argument

To get 'agent_name', 'agent_name','working_area' and 'commission' from the 'agents' table with following condition -

1. 'agent_code' should come in descending order,

then, the following SQL statement can be used :


-- This SQL query retrieves specific columns from the 'agents' table and sorts the result set by the 'agent_code' column in descending order.
-- SELECT statement begins
SELECT 
    agent_code, -- Select the 'agent_code' column from the 'agents' table
    agent_name, -- Select the 'agent_name' column from the 'agents' table
    working_area, -- Select the 'working_area' column from the 'agents' table
    commission -- Select the 'commission' column from the 'agents' table
FROM 
    agents -- Retrieve data from the 'agents' table
ORDER BY 
    agent_code DESC; -- Sort the result set by the 'agent_code' column in descending order

Explanation:

This SQL code is a SELECT statement that retrieves specific columns from the 'agents' table.

The columns selected are 'agent_code', 'agent_name', 'working_area', and 'commission'.

The data is retrieved from the 'agents' table.

The result set is sorted in descending order based on the 'agent_code' column using the ORDER BY clause with the DESC keyword.

The query does not filter or manipulate the data further; it simply retrieves and sorts the specified columns from the 'agents' table.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

Example: SQL - Ordering by more than one columns

You can use the ORDER BY clause to sort the result of a query according to the values in more than one columns.

To get 'agent_name', 'agent_name','working_area' and 'commission' from the 'agents' table with following conditions -

1. 'working_area' should come in ascending order first,

2. within the same 'working_area', 'agent_code' should come in ascending order,

then, the following SQL statement can be used :


-- This SQL query retrieves specific columns from the 'agents' table and sorts the result set first by the 'working_area' column and then by the 'agent_code' column.
-- SELECT statement begins
SELECT 
    agent_code, -- Select the 'agent_code' column from the 'agents' table
    agent_name, -- Select the 'agent_name' column from the 'agents' table
    working_area, -- Select the 'working_area' column from the 'agents' table
    commission -- Select the 'commission' column from the 'agents' table
FROM 
    agents -- Retrieve data from the 'agents' table
ORDER BY 
    working_area, -- Sort the result set first by the 'working_area' column
    agent_code; -- Sort the result set then by the 'agent_code' column

Explanation:

This SQL code is a SELECT statement that retrieves specific columns from the 'agents' table.

The columns selected are 'agent_code', 'agent_name', 'working_area', and 'commission'.

The data is retrieved from the 'agents' table.

The result set is sorted first by the 'working_area' column in ascending order, and then by the 'agent_code' column in ascending order.

Sorting by multiple columns allows for more granular control over the order of the result set, first sorting by 'working_area' and then by 'agent_code'.

The query does not filter or manipulate the data further; it simply retrieves and sorts the specified columns from the 'agents' table.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

Example: SQL - Ordering by more than one columns in ascending or descending order

You can use the ORDER BY clause to sort the result of a query on multiple columns in various order (ascending or descending). Here is an example:

To get 'agent_name', 'agent_name','working_area' and 'commission' from the 'agents' table with following conditions -

1. 'working_area' comes in ascending order first,

2. 'commission' comes in descending order,

then, the following SQL statement can be used :


-- This SQL query retrieves specific columns from the 'agents' table and sorts the result set first by the 'working_area' column in ascending order and then by the 'commission' column in descending order.
-- SELECT statement begins
SELECT 
    agent_code, -- Select the 'agent_code' column from the 'agents' table
    agent_name, -- Select the 'agent_name' column from the 'agents' table
    working_area, -- Select the 'working_area' column from the 'agents' table
    commission -- Select the 'commission' column from the 'agents' table
FROM 
    agents -- Retrieve data from the 'agents' table
ORDER BY 
    working_area ASC, -- Sort the result set first by the 'working_area' column in ascending order
    commission DESC; -- Sort the result set then by the 'commission' column in descending order

Explanation:

This SQL code is a SELECT statement that retrieves specific columns from the 'agents' table.

The columns selected are 'agent_code', 'agent_name', 'working_area', and 'commission'.

The data is retrieved from the 'agents' table.

The result set is sorted first by the 'working_area' column in ascending order and then by the 'commission' column in descending order.

Sorting by multiple columns allows for more granular control over the order of the result set, first sorting by 'working_area' and then by 'commission'.

The query does not filter or manipulate the data further; it simply retrieves and sorts the specified columns from the 'agents' table.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

Example: Handle NULL Values

Be mindful of how NULL values are treated during sorting. By default, NULL values usually appear at the end of the sorted result set. However, you can customize this behavior using NULLS FIRST or NULLS LAST in the ORDER BY clause, depending on your requirements.

Suppose we have a table named employees with columns employee_id, employee_name, and salary. Some employees have NULL values in the salary column.


-- Sample employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO employees (employee_id, employee_name, salary)
VALUES
    (1, 'John Doe', 50000.00),
    (2, 'Jane Smith', NULL),
    (3, 'Michael Johnson', 75000.00),
    (4, 'Emily Davis', 60000.00),
    (5, 'Christopher Lee', NULL);

To order the result set by the salary column while handling NULL values, you can use the COALESCE function to replace NULL values with a specific value before sorting:


-- Select all employees and order them by salary (NULL values last)
SELECT 
    employee_id,
    employee_name,
    salary
FROM 
    employees
ORDER BY 
    COALESCE(salary, 0) DESC; -- Treat NULL values as 0 and sort in descending order

Explanation:

The COALESCE function is used to replace NULL values in the salary column with 0.

By sorting in descending order after replacing NULL values with 0, the NULL values will appear at the end of the sorted result set.

This ensures that employees with NULL salaries are still included in the result set, but they appear after employees with non-NULL salaries.

Output:


+-------------+-----------------+------------+
| employee_id | employee_name   | salary     |
+-------------+-----------------+------------+
| 3           | Michael Johnson | 75000.00   |
| 4           | Emily Davis     | 60000.00   |
| 1           | John Doe        | 50000.00   |
| 2           | Jane Smith      | NULL       |
| 5           | Christopher Lee | NULL       |
+-------------+-----------------+------------+

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL character function TRANSLATE
Next: Group By