Skip to main content
SQL Fundamentals
3 / 5
0%

ORDER BY and LIMIT

0/4 solved4 to unlock next
PrevLocked

Lesson 03

ORDER BY and LIMIT

Lesson Description

Sort rows and trim the result.

Easy Project

Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.

The problem ORDER BY and LIMIT solve

Here is something that surprises a lot of new SQL users: a table has no built-in order. When you run SELECT * FROM employees, the rows come back in whatever order is convenient for the database at that moment. Often that looks like insertion order, but nothing guarantees it, and it can change after updates, vacuums or a different query plan.

Most real questions need a specific order, and many only need the first few rows of it. "Who are our five best-paid employees?" "What are the ten most recent orders?" "Which doctors have been here longest?" ORDER BY sorts the result, and LIMIT cuts it down to a fixed number of rows. Together they answer every "top N" or "latest N" question.

Syntax

SELECT columns
FROM table_name
WHERE condition
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC]
LIMIT n
OFFSET m;
  • ASC (ascending) is the default: smallest number, earliest date, alphabetical text first.
  • DESC (descending) reverses it: largest, latest, reverse alphabetical.
  • LIMIT n keeps at most n rows.
  • OFFSET m skips the first m rows before LIMIT is applied.

The clauses must appear in this order. WHERE before ORDER BY, ORDER BY before LIMIT.

Worked examples

Example 1: sort by one column

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

All 40 employees come back, highest salary first: Steven King (24000), then Lex De Haan and Neena Kochhar (17000 each), then John Russell (14000). Remove DESC and you get the opposite, starting with Karen Colmenares at 2500.

Dates sort chronologically. In the hospital sandbox, this lists the longest-serving doctors first:

SELECT first_name, last_name, specialty, hire_date
FROM doctors
ORDER BY hire_date;

Olivia Bennett (Emergency Physician, hired 2013-11-22) is first, then Priya Patel (2014-06-20) and Carlos Mendoza (2014-09-14).

Example 2: sort by several columns

When you list more than one column, PostgreSQL sorts by the first, and only uses the second to break ties in the first, and so on. Each column gets its own direction:

SELECT department_id, first_name, last_name, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

The result groups employees by department in ascending order (10, 20, 30, ...), and inside each department lists the best-paid person first. Department 10 has only Jennifer Whalen. Department 20 shows Michael Hartstein (13000) above Pat Fay (6000). Department 30 starts with its manager, Den Raphaely (11000), followed by the clerks from 3100 down.

A common slip is to write ORDER BY department_id, salary DESC and expect DESC to apply to both. It does not. Each column defaults to ASC unless you say otherwise.

Example 3: top N with LIMIT

Add LIMIT to keep only the first rows of the sorted result. The store wants to know which products tie up the most money in inventory:

SELECT name, price * stock AS stock_value
FROM products
ORDER BY stock_value DESC
LIMIT 3;

The answer is Wireless Headphones (8999.55), Denim Jeans (8998.50) and Running Sneakers (6749.25). Notice that ORDER BY used the alias stock_value. That is allowed because sorting happens after SELECT has built its columns.

ORDER BY also combines naturally with WHERE. The most recent orders that are still pending:

SELECT id, order_date
FROM orders
WHERE status = 'pending'
ORDER BY order_date DESC
LIMIT 3;

You get orders 1021 (2025-05-12), 1046 (2025-05-11) and 1031 (2025-05-10). The filter runs first, then the sort, then the limit.

Example 4: ties and why you need a tie-breaker

Ask for the two best-paid employees:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2;

The first row is certainly Steven King. The second row is either Lex De Haan or Neena Kochhar, since both earn 17000. The database is free to pick either one, and it may pick differently tomorrow. The query is not wrong, but its output is not deterministic.

The fix is to add a column that is unique as the last sort key:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC, id
LIMIT 2;

Now Neena Kochhar (id 101) always beats Lex De Haan (id 102). Whenever you combine ORDER BY with LIMIT, ask yourself whether ties are possible, and add a tie-breaker if they are. This matters a lot for "exact order" grading on this site and even more in production reports.

Example 5: pagination with OFFSET

Websites show long lists one page at a time. With 5 products per page, page 1 is LIMIT 5 and page 2 is LIMIT 5 OFFSET 5:

SELECT id, name, price
FROM products
ORDER BY price, id
LIMIT 5 OFFSET 5;

Page 2 starts with SQL Antipatterns (id 9), then two Yoga Mats (ids 23 and 26), all at 29.99, then Clean Code (34.50) and The Pragmatic Programmer (39.99). Three products share the same price here, which is exactly why id is in the ORDER BY. Without it, a product could appear on both page 1 and page 2, or on neither.

OFFSET is simple, but the database still has to read and discard every skipped row, so deep pages (say OFFSET 100000) get slow on big tables. For very large lists, teams switch to "keyset" pagination, which filters with WHERE price > last_seen_price. You do not need that here, but it is good to know why.

Under the hood: the end of the pipeline

Recall the logical order from the first lesson: FROM, WHERE, SELECT, DISTINCT, ORDER BY, LIMIT. Sorting and limiting are the last two steps. That has three consequences:

  1. ORDER BY can use aliases from SELECT, as you saw with stock_value.
  2. LIMIT applies to the final, sorted result. LIMIT 5 without ORDER BY gives you five arbitrary rows, not "the first five".
  3. ORDER BY can also sort by a column you did not select, such as ORDER BY hire_date while only showing names (except when DISTINCT is used, covered next lesson).

Where do NULLs go?

PostgreSQL treats NULL as larger than any value. In ascending order NULLs come last, and in descending order they come first. Sorting employees by manager_id DESC puts Steven King, whose manager is NULL, at the very top. You can override this with NULLS FIRST or NULLS LAST:

SELECT first_name, last_name, manager_id
FROM employees
ORDER BY manager_id DESC NULLS LAST;

Now Pat Fay (manager 201) is first, and Steven King moves to the bottom.

Common mistakes

LIMIT before ORDER BY

SELECT first_name, salary
FROM employees
LIMIT 5
ORDER BY salary DESC;

This fails with syntax error at or near "order". The clause order is fixed: ORDER BY first, then LIMIT.

Using an alias inside an expression

Aliases work in ORDER BY only on their own. This fails:

SELECT name, price * stock AS stock_value
FROM products
ORDER BY stock_value * -1;

PostgreSQL reports column "stock_value" does not exist. Either sort by the alias directly (ORDER BY stock_value DESC) or repeat the full expression.

LIMIT without ORDER BY

SELECT name FROM products LIMIT 3 runs fine, and the result might even look sensible. But "the first three rows" has no meaning without a sort, and the answer can change. If the question has the words "top", "first", "latest" or "cheapest", you need ORDER BY.

How other databases differ

DatabaseTop 5 by salary
PostgreSQL, MySQL, SQLiteORDER BY salary DESC LIMIT 5
SQL ServerSELECT TOP 5 ... ORDER BY salary DESC
SQL standard (PostgreSQL, Oracle 12c+)ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY
SQL Server 2012+ alternativeORDER BY salary DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY

NULL placement also differs: MySQL and SQL Server put NULLs first in ascending order, the opposite of PostgreSQL. SQL Server does not support NULLS FIRST / NULLS LAST syntax at all.

Practice

The exercises below this lesson ask for the top earners in HR, the cheapest and the most expensive in-stock products from the store, and an alphabetical employee list sorted on two columns. Pay attention to the sort direction each prompt asks for, and think about whether two rows could tie at the cut-off point.

Key takeaways

  • Without ORDER BY, row order is undefined. Never rely on it.
  • Each ORDER BY column has its own ASC or DESC; the default is ASC.
  • LIMIT keeps the first N rows of the sorted result, and OFFSET skips rows for pagination.
  • Add a unique column (like id) as the last sort key whenever ties could change which rows make the cut.
  • In PostgreSQL, NULLs sort last ascending and first descending; use NULLS FIRST or NULLS LAST to choose.

ORDER BY and LIMIT: in depth

Without ORDER BY, a database may return rows in any order, and that order can change between runs. Sorting makes output deterministic, and LIMIT turns a sorted list into a "top N" answer such as the five highest salaries or the ten most recent orders.

Worked examples

Top five earners with a tie-breaker

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC, last_name ASC
LIMIT 5;

Sorts by salary high to low; employees with equal salaries are ordered by last name so the same five rows come back every time.

Second page of results

SELECT id, order_date
FROM orders
ORDER BY order_date DESC, id DESC
LIMIT 10 OFFSET 10;

Skips the first ten rows and returns the next ten. Including id makes paging stable when several orders share a date.

Common mistakes

  • Using LIMIT without ORDER BY: you get "some" five rows, not the top five, and the set can differ between runs.
  • Forgetting a tie-breaker. Two rows with the same sort value can swap places, so pages overlap or skip rows.
  • Large OFFSET values on big tables: the database still reads and discards every skipped row. Keyset paging (WHERE id < last_seen_id) scales better.

Other databases

  • SQL Server uses SELECT TOP 5 ... or OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY; Oracle 12c+ uses FETCH FIRST 5 ROWS ONLY.
  • PostgreSQL sorts NULLs last in ascending order and first in descending order; add NULLS LAST to control it explicitly.
Check yourself: Why can ORDER BY salary DESC LIMIT 3 return different employees on two runs?

If several employees share the third-highest salary, which one is included is undefined. Add a unique tie-breaker such as id.

Concept check

Quick check: ORDER BY and LIMIT

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Top 5 earners

Enterprise Workforce AnalyticseasyPostgreSQL

Return the first_name, last_name, and salary of the 5 highest-paid employees, ordered by salary descending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Cheapest 3 products

Commerce Operations IntelligenceeasyPostgreSQL

From the products table in the store sandbox, return the name and price of the 3 cheapest products, ordered by price ascending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Employees in alphabetical order

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name and last_name of all employees ordered by last_name ascending, then first_name ascending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 4

Top 3 most expensive in-stock products

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return name and price of the 3 most expensive products that are in stock (stock > 0), ordered by price descending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

SQL Fundamentals
3 / 5
0%

ORDER BY and LIMIT

0/4 solved4 to unlock next
PrevLocked