SQL ORDER BY Clause

-- orders all rows from Customers in ascending order by country
SELECT *
FROM Customers
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,

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.

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.

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.

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.

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