What is ORDER BY clause?

The `ORDER BY` clause in SQL is used to sort the result set of a `SELECT` query in ascending or descending order based on one or more columns.

Purpose of ORDER BY

When you retrieve data from a database using a SELECT statement, the order of the rows in the result set is not guaranteed unless you explicitly specify it. The ORDER BY clause provides a way to sort the data based on one or more columns, making the output more readable and useful for analysis.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Keywords

• ASC: Sorts the result set in ascending order (default). This means smallest to largest for numbers, A to Z for text, and earliest to latest for dates.
• DESC: Sorts the result set in descending order. This means largest to smallest for numbers, Z to A for text, and latest to earliest for dates.

Examples

Sorting in Ascending Order (ASC)

To sort the results by a column in ascending order, you can explicitly use ASC or omit it, as it's the default behavior.

SELECT product_name, price
FROM products
ORDER BY price ASC;

This query will retrieve product names and their prices from the 'products' table, sorted by 'price' from the lowest to the highest.

Sorting in Descending Order (DESC)

To sort the results by a column in descending order, you must use the DESC keyword.

SELECT customer_name, total_orders
FROM customers
ORDER BY total_orders DESC;

This query will list customer names and their total orders from the 'customers' table, sorted by 'total_orders' from the highest to the lowest.

Sorting by Multiple Columns

You can sort by multiple columns. The sorting is applied sequentially: first by the primary sort column, then by the second for rows with identical values in the first column, and so on.

SELECT last_name, first_name, city
FROM employees
ORDER BY last_name ASC, first_name DESC;

In this example, the results are first sorted by 'last_name' in ascending order. If two employees have the same last name, they are then sorted by 'first_name' in descending order.

Position in a Query

The ORDER BY clause is typically the last clause in a SELECT statement, following FROM, WHERE, GROUP BY, and HAVING clauses.