SQL ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result set in ascending or descending order.

Example

-- orders all rows from Customers in ascending order by country
SELECT *
FROM Customers
ORDER BY country;

Here, the SQL command selects all rows from the Customers table and then sorts them in ascending order by country.


SQL ORDER BY Syntax

The syntax of the SQL ORDER BY statement is:

SELECT column1, column2, ...
FROM table
ORDER BY columnA, columnB, ...;

Here,

  • column1, column2, ... are the columns to be included in the result set
  • table is the name of the table from where the rows are selected
  • columnA, columnB, ... are the column(s) based on which the rows will be ordered

For example,

How to use ORDER BY in SQL
Example: ORDER BY in SQL

ORDER BY ASC (Ascending Order)

We can use the ASC keyword to explicitly sort selected records in ascending order. For example,

-- orders all rows from Customers in ascending order by age 
SELECT *
FROM Customers
ORDER BY age ASC;

Here, the SQL command selects all the rows from Customers table and then sorts them in ascending order by age.

How to use ORDER BY with ASC in SQL
Example: ORDER BY ASC in SQL

Note: The ORDER BY clause sorts result set in ascending by default, even without the ASC clause.


ORDER BY DESC (Descending Order)

We use the DESC keyword to sort the selected records in descending order. For example,

-- order all rows from Customers in descending order by age
SELECT *
FROM Customers
ORDER BY age DESC;

Here, the SQL command selects all the customers and then sorts them in descending order by age.

How to use ORDER BY with DSC in SQL
Example: ORDER BY DESC in SQL

ORDER BY With Multiple Columns

We can also use ORDER BY with multiple columns. For example,

-- sort all rows from Customers, first by first_name and then by age
SELECT *
FROM Customers
ORDER BY first_name, age;

Here, the SQL command selects all the records and then sorts them by first_name. If the first_name repeats more than once, it sorts those records by age.

How to use ORDER BY with multiple columns in SQL
Example: SQL ORDER BY with multiple columns

ORDER BY With WHERE

We can also use ORDER BY with the SELECT WHERE clause. For example,

-- select last_name and age of customers who don't live in the UK
-- and sort them by last_name in descending order

SELECT last_name, age
FROM Customers
WHERE NOT country = 'UK'
ORDER BY last_name DESC;

Here,

  • The SQL command first selects the last_name and age fields from the Customers table if their country is not UK.
  • Then, the selected records are sorted in descending order by their last_name.
How to use ORDER BY with WHERE in SQL
Example: ORDER BY with WHERE in SQL

Note: When using the WHERE clause with ORDER BY, the WHERE clause always comes first.

Did you find this article helpful?