ORDER BY, LIMIT & OFFSET

Overview

Without ORDER BY, SQL returns rows in no guaranteed order — the same query can return rows in a different sequence each time. ORDER BY sorts the output row-set by one or more columns. ASC (ascending, the default) sorts low-to-high; DESC sorts high-to-low: SELECT name, price FROM products ORDER BY price DESC; Returns 6 rows: Monitor (199.99), Desk (149.99), Chair (89.99), Keyboard (49.99), Mouse (19.99), Notebook (4.99). Multi-column sorting uses the second column as a tiebreaker: SELECT name, category, price FROM products ORDER BY category ASC, price DESC; Sorts by category alphabetically first, then within each category sorts by price highest-first. Electronics: Monitor, Keyboard, Mouse. Furniture: Desk, Chair. Stationery: Notebook. NULL values sort last by default in ASC (first in DESC). You can override this with NULLS FIRST or NULLS LAST. LIMIT restricts how many rows are returned: SELECT name, price FROM products ORDER BY price DESC LIMIT 3; Returns only the 3 most expensive products: Monitor, Desk, Chair. OFFSET skips rows before returning, enabling pagination: SELECT name, price FROM products ORDER BY price DESC LIMIT 3 OFFSET 3; Skips the first 3 and returns the next 3: Keyboard, Mouse, Notebook. In the logical query flow: FROM → WHERE → SELECT → ORDER BY → LIMIT/OFFSET. ORDER BY and LIMIT run last — they shape the final output after all filtering and column selection is done.

Every paginated API endpoint uses ORDER BY + LIMIT + OFFSET (or cursor-based equivalents). Without ORDER BY, pagination is meaningless — pages would contain random rows. Top-N queries (top 5 customers, cheapest 10 products) are impossible without ORDER BY + LIMIT.

Where used: paginated REST API endpoints, top-N dashboards and reports, leaderboard queries

Why learn this

• Every paginated API list endpoint translates page/size parameters into LIMIT and OFFSET
• Top-N analytics queries (best sellers, highest-spending customers) require ORDER BY + LIMIT

Query walkthrough

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Focus: Watch ORDER BY sort all 6 rows by price descending, then LIMIT chop the result to only the top 3.

Common mistakes

• Using OFFSET without ORDER BY: Without ORDER BY, the row order is undefined, so OFFSET skips arbitrary rows. Always pair OFFSET with ORDER BY to get deterministic pagination.
• Forgetting that OFFSET-based pagination gets slower on large tables: OFFSET 10000 LIMIT 10 forces the database to skip 10000 rows. For large datasets, cursor-based pagination (WHERE id > last_seen_id ORDER BY id LIMIT 10) is much faster.
• Assuming NULL sorts first in ASC: In PostgreSQL, NULLs sort LAST in ASC order (and FIRST in DESC). If you need NULLs at the top, use NULLS FIRST explicitly.

Glossary

tiebreaker

A rule or condition used to decide the order when two items have the same value for the primary sorting criteria.

pagination

The process of dividing a large set of results into smaller, separate pages, like showing 10 items per page.

deterministic

Always producing the exact same result every time a process is run with the same inputs.

Recall questions

• What is the default sort direction if you write ORDER BY price without ASC or DESC?
• Where do NULLS appear by default when sorting ASC in PostgreSQL?
• What does LIMIT 3 OFFSET 3 do?

Understanding checks

What 4 rows does this return?

Farah (AE), Hana (JP), Carlos (MX), Diana (UK).

ASC sorts alphabetically by country. AE < JP < MX < UK < US. NULLs sort last in ASC, so Emma (NULL) comes after all non-NULL countries. LIMIT 4 takes the first 4: Farah, Hana, Carlos, Diana.

Which products does this return?

Desk (149.99), Monitor (199.99).

All 6 products sorted by price ASC: Notebook (4.99), Mouse (19.99), Keyboard (49.99), Chair (89.99), Desk (149.99), Monitor (199.99). OFFSET 4 skips the first 4, LIMIT 2 takes the next 2.

Practice tasks

Get page 2 of products

This query returns the 3 cheapest products (page 1). Modify it to return page 2 instead — the next 3 cheapest products — by adding an OFFSET clause. Keep the same ordering and limit.

Challenge

Most recent 3 paid orders

Write a query that returns the id and order_date of the 3 most recent orders with status 'paid'. Sort by order_date descending.

Continue learning

Previous: SELECT, FROM & Columns

Previous: WHERE & Operators

Next: OVER & PARTITION BY

Return to SQL Roadmap