SQL ORDER BY Clause

SQL ORDER BY Clause

The ORDER BY clause is used to sort the rows returned by a SELECT query.
By default, SQL returns data in no guaranteed order, so if you want organised results — alphabetical, numeric, highest to lowest, earliest to latest — you must use ORDER BY.

This tutorial explains how to sort data using one or multiple columns in both ascending and descending order.

1. Basic Syntax of ORDER BY

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

ASC → Ascending order (default)

DESC → Descending order

You can sort by one or many columns

Numeric, text, date, and expression columns can all be sorted

2. Sorting by One Column

-- Example: Sort employees by their first name:

SELECT FirstName, LastName, Salary

FROM Employees

ORDER BY FirstName;

-- Because ascending order is default, the result is A → Z.

-- To reverse the order:

SELECT FirstName, LastName, Salary

FROM Employees

ORDER BY FirstName DESC;

3. Sorting by Multiple Columns

When two or more rows have the same value in the first column, SQL uses the next column to break the tie.

-- Example:

SELECT FirstName, LastName, City

FROM Employees

ORDER BY City ASC, LastName ASC;

Meaning:

• Sort by City first
  
• If two people live in the same city → sort by LastName
  

-- Another example (common in banking/salary data):

SELECT Department, Salary, FirstName

FROM Employees

ORDER BY Department ASC, Salary DESC;

4. Sorting by an Expression

You can sort by a calculated column, even if it’s not shown in the result.

-- Example: Sort employees by annual salary (calculated on the fly):

SELECT 

    FirstName,

    Salary,

    Salary * 12 AS AnnualSalary

-- FROM Employees

ORDER BY Salary * 12 DESC;

-- Or sort by length of employee names:

SELECT FirstName, LastName

FROM Employees

ORDER BY LENGTH(FirstName);

5. Sorting by Column Position (Not Recommended Usually)

SQL allows sorting using the column number in the SELECT list:

SELECT FirstName, LastName, Salary

FROM Employees

ORDER BY 3 DESC;

Here, 3 means the third column in the SELECT list → Salary.

Although allowed, this is not recommended because:

• Harder to read
  
• Risky when someone changes the SELECT column order later
  

Use column names for clarity.

6. ORDER BY with NULL Values

SQL does not treat NULL as a normal value.
Sorting behaviour:

• In ASC, NULL appears first in most databases
  

• In DESC, NULL appears last
  

-- Example:

SELECT FirstName, Bonus

FROM Employees

ORDER BY Bonus ASC;

-- To explicitly control placement:

-- NULLS LAST (PostgreSQL, Oracle):

SELECT FirstName, Bonus

FROM Employees

ORDER BY Bonus ASC NULLS LAST;

-- NULLS FIRST (PostgreSQL, Oracle):

ORDER BY Bonus DESC NULLS FIRST;

(MySQL doesn’t support NULLS FIRST/LAST, but you can simulate it with conditions.)

7. ORDER BY with Aliases

You can sort using the alias defined in the SELECT clause.

SELECT

    FirstName,

    LastName,

    Salary * 12 AS AnnualSalary

FROM Employees

ORDER BY AnnualSalary DESC;

This improves readability, especially with complex calculations.

8. ORDER BY with LIMIT / TOP / FETCH

Sorting is usually combined with row-limiting clauses.

-- MySQL / PostgreSQL:

SELECT *

FROM Employees

ORDER BY Salary DESC

LIMIT 5;

-- SQL Server:

SELECT TOP 5 *

FROM Employees

ORDER BY Salary DESC;

-- Standard SQL:

SELECT *

FROM Employees

ORDER BY Salary DESC

FETCH FIRST 5 ROWS ONLY;

9. Practical Example (Real-World Scenario)

Imagine a Transactions table:

-- Sort by latest transactions:

SELECT TxnID, CustomerID, Amount, TxnDate

FROM Transactions

ORDER BY TxnDate DESC;

-- Sort by amount (biggest first), NULL amounts last:

SELECT TxnID, Amount, TxnDate

FROM Transactions

ORDER BY Amount DESC NULLS LAST;

-- Sort by type and then by date:

SELECT TxnID, Type, Amount, TxnDate

FROM Transactions

ORDER BY Type ASC, TxnDate DESC;

10. Summary

By now you should understand:

✔ How to sort rows using ORDER BY

✔ Difference between ASC and DESC

✔ Sorting with multiple columns

✔ Sorting by expressions and aliases

✔ Handling NULLs in sorting

✔ How ORDER BY works with LIMIT/TOP

The ORDER BY clause is one of the most important tools for analysing and presenting data accurately.