ORDER BY — Sorting Results
SQL tables are unordered sets. Without ORDER BY, the database returns rows in any order it chooses — which can change between queries. If order matters, always specify it explicitly.
Basic Syntax
SELECT columns
FROM table
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
ASC— ascending order (smallest first, A→Z) — defaultDESC— descending order (largest first, Z→A)
Sample Data
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT NOT NULL,
salary REAL NOT NULL,
hire_date TEXT NOT NULL
);
INSERT INTO employees VALUES
(1, 'Priya Sharma', 'Finance', 75000, '2022-03-15'),
(2, 'Raj Patel', 'Technology', 92000, '2021-07-01'),
(3, 'Meera Singh', 'Marketing', 68000, '2023-01-10'),
(4, 'Arjun Nair', 'Finance', 81000, '2020-11-22'),
(5, 'Sunita Rao', 'Technology', 88000, '2022-09-05'),
(6, 'Dev Kumar', 'Marketing', 72000, '2021-04-18'),
(7, 'Kiran Mehta', 'Finance', 95000, '2019-06-30'),
(8, 'Ananya Das', 'Technology', 63000, '2024-02-14');
Sorting by One Column
-- Alphabetical by name (A→Z)
SELECT name, salary
FROM employees
ORDER BY name ASC;
-- Highest salary first
SELECT name, salary
FROM employees
ORDER BY salary DESC;
-- Earliest hire date first
SELECT name, hire_date
FROM employees
ORDER BY hire_date ASC;
Sorting by Multiple Columns
Sort by a primary key, then break ties with a secondary key:
-- Sort by department alphabetically, then by salary (highest first) within each department
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
Result:
name | department | salary
-------------+------------+-------
Kiran Mehta | Finance | 95000 ← Finance, highest first
Arjun Nair | Finance | 81000
Priya Sharma | Finance | 75000
Dev Kumar | Marketing | 72000 ← Marketing
Meera Singh | Marketing | 68000
Raj Patel | Technology | 92000 ← Technology, highest first
Sunita Rao | Technology | 88000
Ananya Das | Technology | 63000
Each column in ORDER BY can have its own direction:
-- Department A→Z, then within each department hire_date newest first
ORDER BY department ASC, hire_date DESC;
Sorting by Column Position
You can reference columns by their position in the SELECT list:
SELECT name, salary, department
FROM employees
ORDER BY 2 DESC; -- same as ORDER BY salary DESC
This is generally discouraged — position-based references break silently if you reorder the SELECT columns. Prefer column names.
Sorting by Expression or Alias
You can sort by a computed expression or alias:
-- Sort by monthly salary
SELECT name, salary, ROUND(salary / 12, 2) AS monthly
FROM employees
ORDER BY monthly DESC;
-- Sort by name length
SELECT name
FROM employees
ORDER BY LENGTH(name);
NULL Handling in ORDER BY
By default:
- PostgreSQL: NULLs sort last in ASC, first in DESC
- MySQL/SQLite: NULLs sort first in ASC, last in DESC
Control NULL position explicitly (PostgreSQL):
-- NULLs at the end regardless of sort direction
ORDER BY manager_id DESC NULLS LAST;
-- NULLs at the beginning
ORDER BY manager_id ASC NULLS FIRST;
LIMIT — Return Only N Rows
LIMIT restricts how many rows are returned. Combine with ORDER BY to get top-N results:
-- Top 3 earners
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
Result:
name | salary
------------+-------
Kiran Mehta | 95000
Raj Patel | 92000
Sunita Rao | 88000
Important:
LIMITwithoutORDER BYreturns an arbitrary set of rows. Always pair them.
MySQL / SQLite: LIMIT
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;
SQL Server / Oracle: TOP / FETCH FIRST
-- SQL Server
SELECT TOP 5 name, salary FROM employees ORDER BY salary DESC;
-- Oracle / Standard SQL
SELECT name, salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;
OFFSET — Skipping Rows (Pagination)
OFFSET skips a number of rows before starting to return results. Use LIMIT + OFFSET together for pagination:
-- Page 1: rows 1-3
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 0;
-- Page 2: rows 4-6
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;
-- Page 3: rows 7-9
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 6;
Pagination Formula
OFFSET = (page_number - 1) × page_size
So for page 4 with 10 rows per page: LIMIT 10 OFFSET 30
SQL Server OFFSET-FETCH
-- SQL Server (requires ORDER BY with OFFSET-FETCH)
SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
DISTINCT — Removing Duplicates
DISTINCT removes duplicate rows from the result. It applies to the entire row (all selected columns together):
-- All unique departments
SELECT DISTINCT department
FROM employees;
department
----------
Finance
Technology
Marketing
DISTINCT on Multiple Columns
-- All unique department + salary combinations
SELECT DISTINCT department, salary
FROM employees
ORDER BY department, salary;
This returns each unique (department, salary) pair — not unique departments or unique salaries independently.
DISTINCT vs GROUP BY
For simple deduplication, DISTINCT and GROUP BY produce the same result:
-- Both return unique departments
SELECT DISTINCT department FROM employees;
SELECT department FROM employees GROUP BY department;
Use DISTINCT for simple deduplication; use GROUP BY when you also need aggregate functions (counts, sums, etc.).
Combining ORDER BY, LIMIT, and WHERE
-- Top 3 Finance earners
SELECT name, salary
FROM employees
WHERE department = 'Finance'
ORDER BY salary DESC
LIMIT 3;
-- Most recently hired Marketing employee
SELECT name, hire_date
FROM employees
WHERE department = 'Marketing'
ORDER BY hire_date DESC
LIMIT 1;
Practical Examples
Example 1: Employee Leaderboard
-- Top 5 earners with rank-like numbering
SELECT
name,
department,
salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
Example 2: Newest Hires by Department
-- Most recently hired in each department
-- (Simple version — one query per department)
SELECT name, department, hire_date
FROM employees
WHERE department = 'Technology'
ORDER BY hire_date DESC
LIMIT 1;
Example 3: Paginated Employee List
-- Page 2 of employees, sorted alphabetically, 3 per page
SELECT id, name, department, salary
FROM employees
ORDER BY name ASC
LIMIT 3 OFFSET 3;
Example 4: Unique Departments Sorted Alphabetically
SELECT DISTINCT department
FROM employees
ORDER BY department ASC;
Logical Order of Clauses
The complete SQL SELECT clause order, showing when each one is processed:
1. FROM — identify the table(s)
2. WHERE — filter rows
3. GROUP BY — group the filtered rows
4. HAVING — filter the groups
5. SELECT — compute the output columns
6. DISTINCT — remove duplicates
7. ORDER BY — sort the result
8. LIMIT/OFFSET — return a subset
This is why aliases defined in SELECT can be used in ORDER BY (which runs after SELECT), but not in WHERE (which runs before SELECT).
Common Mistakes
1. LIMIT without ORDER BY
-- Wrong — which 3 rows? It's arbitrary and can change
SELECT name FROM employees LIMIT 3;
-- Correct — deterministic result
SELECT name FROM employees ORDER BY id LIMIT 3;
2. Forgetting OFFSET when paginating
-- Wrong — always returns page 1 no matter what
SELECT name FROM employees ORDER BY id LIMIT 10;
-- Correct — add OFFSET for pages beyond the first
SELECT name FROM employees ORDER BY id LIMIT 10 OFFSET 20; -- page 3
3. Confusing ASC and DESC direction
-- "Latest employees" should use DESC on hire_date
SELECT name, hire_date FROM employees ORDER BY hire_date DESC LIMIT 5;
-- Ascending would give the oldest, not the latest
4. DISTINCT applies to all columns, not just the first one
-- This does NOT give distinct departments
-- It gives distinct (department, salary) pairs
SELECT DISTINCT department, salary FROM employees;
Practice Exercises
- List all employees sorted alphabetically by name (A→Z).
- Find the 3 employees with the lowest salary.
- Show employees sorted by department (A→Z), then by hire date (newest first within each department).
- Display the 2nd and 3rd most recently hired employees (use OFFSET).
- List all unique department values from the table, sorted in reverse alphabetical order.
Summary
In this chapter you learned:
ORDER BY column ASC|DESC— sort rows; default is ASC- Multiple columns in ORDER BY break ties left to right
- Each column in ORDER BY can have its own ASC or DESC
LIMIT n— return at most n rows (always pair with ORDER BY)OFFSET n— skip n rows, used with LIMIT for paginationDISTINCT— removes duplicate rows from the result setDISTINCTapplies to all selected columns combined, not individually- SQL clause logical execution order: FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT
Next up: Aggregate Functions — learn to calculate totals, averages, and counts across groups of rows.