The SQL ORDER BY clause is used to sort the result set in either ascending or descending order. For example,
SELECT *
FROM Customers
ORDER BY first_name;
Here, the SQL command selects all customers and then sorts them in ascending order by first_name.
ORDER BY ASC (Ascending Order)
We can use the ASC keyword explicitly to sort selected records in ascending order. For example,
SELECT *
FROM Customers
ORDER BY age ASC;
Here, the SQL command selects all the customers and then sorts them in ascending order by age.
Note: The ORDER BY clause sorts result set in ascending by default; it's not necessary to use ASC explicitly.
ORDER BY DESC (Descending Order)
We use the DESC keyword to sort the selected records in descending order. For example,
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,
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, age
FROM Customers
WHERE NOT country = 'UK'
ORDER BY last_name DESC;
Here,
- The SQL command first selects 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: The WHERE clause must appear before the ORDER BY clause, while using the WHERE clause with ORDER BY.