SQL Order By

The ORDER BY clause is used to sort the result-set of a query in either ascending or descending order.

By default, SQL retrieves rows from a table in an unpredictable order (the order they were inserted). ORDER BY allows you to arrange the data clearly by one or more columns.

Basic Syntax

The ORDER BY clause always goes at the very end of your query (just before a LIMIT or TOP clause if you have one).

SELECT column1, column2
FROM table_name
ORDER BY column_name [ASC | DESC];

• ASC (Default): Sorts data in ascending order (A to Z, 1 to 10, oldest date to newest date).
• DESC: Sorts data in descending order (Z to A, 10 to 1, newest date to oldest date).

Real-World Examples

Sort Alphabetically (A to Z)

-- Sorts customers by their last name from A to Z
SELECT first_name, last_name FROM customers
ORDER BY last_name ASC;
-- (You can omit 'ASC' as it is the default)

Sort Numerically (Highest to Lowest)

-- Finds the most expensive products first
SELECT product_name, price FROM products
ORDER BY price DESC;

Sort by Dates (Newest First)

-- Displays the most recent orders at the top of the list
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC;

3. Sorting by Multiple Columns

You can specify multiple columns in the ORDER BY clause, separated by commas. SQL will sort by the first column listed, and then use the subsequent columns to break any "ties."

-- Sorts employees by department (A-Z).
-- If multiple employees are in the same department, it sorts them by salary (highest to lowest).
SELECT department, first_name, salary
FROM employees
ORDER BY department ASC, salary DESC;

Sorting by Column Position (Short-hand)

Instead of typing out long column names, you can refer to the columns by their numerical position in the SELECT statement (starting at 1).

SELECT first_name, last_name, email
FROM employees
ORDER BY 2 ASC; -- This automatically sorts by 'last_name' because it is the 2nd column

Note: While convenient for quick ad-hoc queries, using numbers is generally discouraged in production code because adding or removing columns from your SELECT statement will break the sorting logic

Syntax Placement Rule

When writing a comprehensive query, ORDER BY must follow this exact order:

1. SELECT
2. FROM
3. WHERE (if filtering rows)
4. GROUP BY / HAVING (if aggregating data)
5. ORDER BY
6. LIMIT / TOP (if capping results)