Order, limit & aliases.
Turn rows into reports.
A raw query returns rows in arbitrary order. A useful query returns them sorted, limited, and labeled. That's what ORDER BY, LIMIT, and AS do. Three clauses that turn "give me data" into "give me the top 10 customers by revenue, sorted descending, labeled for the report." Every example has a Code / Output toggle — click to see the sorted result.
The full SELECT order: SELECT columns FROM table WHERE condition ORDER BY column LIMIT n. ORDER BY sorts (ASC default, DESC for reverse). LIMIT restricts row count. OFFSET skips rows for pagination. AS renames columns or tables in the output. The critical rule: LIMIT without ORDER BY is meaningless — you get n arbitrary rows, not n sorted rows. Always pair them.
Before this lesson: complete Lesson 1. You need SELECT and WHERE first.
📊 ORDER BY — sort results
Without ORDER BY, row order is arbitrary. With it, you control the order.
SQL doesn't guarantee row order unless you specify it. Even if a table happens to return rows in insertion order today, a new index, a database upgrade, or a query planner change can reorder them tomorrow. Always use ORDER BY when order matters.
-- ASC is the default (A → Z, 0 → 9) SELECT name, age FROM users ORDER BY age ASC;
| name | age |
|---|---|
| Sofia García | 26 |
| Alice Chen | 28 |
| Priya Patel | 31 |
| Bob Smith | 34 |
| Marcus Lee | 42 |
Flip the direction with DESC — the most common case for "top" queries:
-- DESC reverses the order (Z → A, 9 → 0) SELECT name, age FROM users ORDER BY age DESC;
| name | age |
|---|---|
| Marcus Lee | 42 |
| Bob Smith | 34 |
| Priya Patel | 31 |
| Alice Chen | 28 |
| Sofia García | 26 |
Sort by multiple columns — the first takes priority, the second breaks ties:
-- Sort by country (A → Z), then by age (oldest first) SELECT name, country, age FROM users ORDER BY country ASC, age DESC;
| name | country | age |
|---|---|---|
| Sofia García | ES | 26 |
| Priya Patel | IN | 31 |
| Bob Smith | UK | 34 |
| Marcus Lee | US | 42 |
| Alice Chen | US | 28 |
ASC (default)
Ascending — smallest first. A → Z for strings, 0 → 9 for numbers, earliest → latest for dates. Explicit is optional but clearer in some cases.
DESC
Descending — largest first. Z → A for strings, 9 → 0 for numbers, latest → earliest for dates. The default for "top N" queries where you want biggest/most recent.
Multiple columns
ORDER BY a ASC, b DESC. First sorts by a, then within ties by b. The order of columns matters — it's the priority order.
Sort by column not in SELECT
You can ORDER BY age even if age isn't in the SELECT list. Useful for "show names sorted by signup date" — you don't need to display the date.
Always specify ORDER BY when order matters. A query without ORDER BY happens to return rows in some order today — but the database is free to change that order at any time. A new index, a version upgrade, or just a different execution plan can reorder results. Code that depends on unspecified order is a bug waiting to happen. If your application displays a list, sort it in SQL (not in application code) — the database is faster at it, and the guarantee is stronger.
✂️ LIMIT — pick the top N rows
Return at most N rows. Combined with ORDER BY, this is how "top N" queries work.
LIMIT caps how many rows are returned. On its own, it returns N arbitrary rows — not useful. Combined with ORDER BY, it answers every "top N" question: top 10 customers by revenue, most recent 5 orders, highest-rated 3 products.
-- Top 3 oldest users SELECT name, age FROM users ORDER BY age DESC LIMIT 3;
| name | age |
|---|---|
| Marcus Lee | 42 |
| Bob Smith | 34 |
| Priya Patel | 31 |
Most common use case in production: "most recent N rows." Sort by a date column descending, then limit:
-- The 5 most recent users SELECT name, created_at FROM users ORDER BY created_at DESC LIMIT 5;
| name | created_at |
|---|---|
| Sofia García | 2026-05-03 |
| Marcus Lee | 2026-04-12 |
| Priya Patel | 2026-03-08 |
| Bob Smith | 2026-02-20 |
| Alice Chen | 2026-01-15 |
LIMIT n
Return at most n rows. If fewer match, returns what's there. If n is larger than the row count, returns all rows. Safe to use without a count check.
LIMIT with ORDER BY
The pair is meaningless without ORDER BY — you get n arbitrary rows. The order matters when the limit is applied (after ORDER BY, before returning).
LIMIT 0
Returns zero rows. Useful for "does this query work?" checks — you see the column names without fetching data. Rarely used in production.
Database differences
PostgreSQL, MySQL, SQLite: LIMIT. SQL Server: TOP. Oracle: FETCH FIRST N ROWS ONLY. Same concept, different syntax.
LIMIT without ORDER BY is not "top N." SELECT * FROM users LIMIT 10 returns 10 arbitrary rows — not the 10 most recent, not the 10 oldest, not the 10 highest. The database picks 10 rows based on whatever plan is fastest. It might return the same 10 rows every time (because the plan is deterministic), or it might not. Never assume LIMIT without ORDER BY gives you meaningful rows. Always sort first.
⏭️ OFFSET — paginate through results
Skip N rows. The other half of pagination. And the source of a common performance bug.
-- Page 1: first 2 users (sorted by age) SELECT name, age FROM users ORDER BY age ASC LIMIT 2 OFFSET 0; -- Page 2: users 3-4 SELECT name, age FROM users ORDER BY age ASC LIMIT 2 OFFSET 2; -- Page 3: users 5-6 SELECT name, age FROM users ORDER BY age ASC LIMIT 2 OFFSET 4;
Page 1 (LIMIT 2 OFFSET 0)
| name | age |
|---|---|
| Sofia García | 26 |
| Alice Chen | 28 |
Page 2 (LIMIT 2 OFFSET 2)
| name | age |
|---|---|
| Priya Patel | 31 |
| Bob Smith | 34 |
Page 3 (LIMIT 2 OFFSET 4)
| name | age |
|---|---|
| Marcus Lee | 42 |
OFFSET n
Skips the first n rows before returning results. Combined with LIMIT, gives page N of results. Page 1 = OFFSET 0, page 2 = OFFSET limit, page N = OFFSET (N-1) × limit.
Last page has fewer rows
If the total row count doesn't divide evenly, the last page has fewer rows. Your application needs to handle this gracefully (don't assume every page has exactly LIMIT rows).
OFFSET is slow on large tables
The database scans and discards the first N rows. OFFSET 10000 requires reading 10000 rows before returning any results. Fine for hundreds of rows, painful for millions.
Keyset pagination
The fast alternative: WHERE id > last_seen_id ORDER BY id LIMIT 10. Jumps straight to the position via the index — no scanning. Use for large datasets.
OFFSET pagination breaks when data changes between pages. User views page 1, someone adds a new row, user views page 2 — they see the same row twice (once on page 1, once on page 2) because the offset shifted. This is called "phantom reads" and it's a known pagination bug. Fix: keyset pagination (WHERE id > last_seen_id) or snapshot the query at a point in time. For customer-facing lists where new rows appear, keyset is the standard.
🏷️ AS — rename for the output
Column aliases rename output. Table aliases shorten queries. Both use AS.
-- Column alias — rename in output SELECT name AS user_name, age AS years_old, country AS country_code FROM users; -- Computed column alias — useful for reports SELECT name, age, age * 12 AS age_in_months FROM users; -- Aliases can have spaces (quote them) SELECT name AS "User Name", age AS "Years Old" FROM users;
Result of the last query: aliases with spaces
| User Name | Years Old |
|---|---|
| Alice Chen | 28 |
| Bob Smith | 34 |
| Priya Patel | 31 |
| Marcus Lee | 42 |
| Sofia García | 26 |
Column aliases
SELECT name AS user_name. The output has "user_name" instead of "name". Useful for reports, APIs, and readable output. The actual column doesn't change.
Computed columns
SELECT price * quantity AS total. Arithmetic, string concatenation, functions — anything can be aliased. Essential for calculated metrics in reports.
Table aliases
FROM users AS u. Shortens queries with JOINs: SELECT u.name, o.total FROM users u JOIN orders o ON .... Reduces typing and improves readability.
AS is optional
SELECT name user_name works in most databases. But use AS — it's clearer and prevents mistakes. The three-character savings isn't worth the readability cost.
Alias computed columns in every report. SELECT price * quantity AS total produces a column header of "total" in your output. Without the alias, it's "price * quantity" — ugly in every export to CSV, Excel, or a dashboard. For anything user-facing, aliases are mandatory. Even for internal use, they make debugging easier.
🏆 Top N queries — the pattern
The most common report. Sort descending, limit, done.
-- Top 3 highest spenders SELECT name, total_spent AS "Total Spent ($`)" FROM users WHERE total_spent > 0 ORDER BY total_spent DESC LIMIT 3; -- 5 most recent orders SELECT id, user_id, amount AS "Amount (`$)", created_at AS "Date" FROM orders ORDER BY created_at DESC LIMIT 5; -- 10 cheapest products in stock SELECT name, price AS "Price (latex )" FROM products WHERE stock > 0 ORDER BY price ASC LIMIT 10;</pre> </div> <div class="code-output-pane" data-pane="output"> <div style="padding:1rem;background:#060f1c;"> <p style="color:#64748b;font-size:0.72rem;margin:0 0 6px 0;text-transform:uppercase;letter-spacing:0.05em;font-weight:700;">Result of query 1: Top 3 highest spenders</p> <table class="sql-output-table"> <thead> <tr><th>name</th><th>Total Spent ( )Marcus Lee 2,847.50 Alice Chen 1,932.00 Priya Patel 1,205.75 3 rows returned (LIMIT 3)
Top N highest
ORDER BY amount DESC LIMIT n. Top customers, top products, highest scores. The most common report pattern.
Top N lowest
ORDER BY price ASC LIMIT n. Cheapest products, lowest scores, oldest items. Used for "bargain" and "urgent" lists.
Most recent N
ORDER BY created_at DESC LIMIT n. Recent orders, latest signups, newest messages. Used everywhere — activity feeds, notifications.
Oldest N
ORDER BY created_at ASC LIMIT n. Oldest accounts, pending items, overdue tasks. Used for "needs attention" queues.
The "Top N" pattern is the single most-used SQL query in production. Every analytics dashboard, every "recent activity" feed, every "leaderboard" widget uses ORDER BY ... DESC LIMIT n. It's the answer to "show me the most X." Once you internalize this pattern, you can answer almost every product question that starts with "who has the most" or "what was the last."
🏆 Challenge
Three tasks. Each exercises a different sort/limit pattern.
Task 1 — Basic queries
Given a products table with columns id, name, price, stock, created_at:
1. Return all products sorted by price ascending
2. Return the 5 most expensive products
3. Return the 10 most recently added products
4. Return products sorted by category then by price descending
5. Return the 3 cheapest products that are in stock
For each query, predict what the output would look like — how many rows, in what order.
Task 2 — Pagination
Given a table with 100 rows, write queries for:
1. Page 1 with 10 rows per page
2. Page 5 with 20 rows per page
3. The 3rd page with 25 rows per page
4. The last page (assume 10 per page, 100 rows total)
For each, what's the LIMIT and OFFSET? What happens if the total row count is 95 instead of 100 — how does the last page change?
Task 3 — Multi-clause query
Write a query that answers this question:
"Give me the top 5 highest-spending customers from the US who have placed more than 3 orders, sorted by total spending descending, with columns: name, total spent (aliased), number of orders (aliased), and last order date (aliased)."
Break it down:
• WHERE conditions: country = 'US' AND order_count > 3
• SELECT columns with aliases: name AS "Customer", total_spent AS "Total ($)", etc.
• ORDER BY: total_spent DESC
• LIMIT: 5
This is a real production query — the kind you'd write for a sales dashboard. It combines SELECT, WHERE, ORDER BY, LIMIT, and aliases.
⚠️ Common mistakes
The sort/limit/alias bugs that catch every beginner.
LIMIT 10 without ORDER BY gives 10 arbitrary rows. Not the top 10, not the most recent 10 — just any 10. Always pair LIMIT with ORDER BY.
Default is ASC. ORDER BY revenue LIMIT 10 gives the 10 LOWEST revenues, not the highest. Fix: ORDER BY revenue DESC LIMIT 10.
LIMIT 10 OFFSET 100000 scans 100,000 rows before returning any. Fix: keyset pagination (WHERE id > last_id LIMIT 10).
ORDER BY 2 sorts by the 2nd column in SELECT. Works but fragile — adding a column shifts the number. Fix: ORDER BY column_name.
AS 'alias' (single quotes) treats the alias as a string literal in some databases, not an identifier. Fix: AS alias (no quotes) or AS "alias" (double quotes) for aliases with spaces.
NULLs sort first or last depending on the database. If your "top 10" query includes NULL values, they might appear at the top. Fix: WHERE amount IS NOT NULL, or use NULLS LAST (PostgreSQL).
ORDER BY age DESC, country ASC sorts by age first, then country. If you wanted country first, the order is wrong. Fix: ORDER BY country ASC, age DESC.
AS order or AS table — these are SQL keywords and may cause errors. Fix: AS "order" (quoted) or better, use a different alias (AS order_info).
➡️ What's next
You can read, filter, sort, and paginate. Next: combining multiple tables.
❓ Frequently asked questions
What does ORDER BY do in SQL?
ORDER BY sorts the result rows. Without it, SQL returns rows in arbitrary order. ORDER BY column ASC sorts ascending. ORDER BY column DESC sorts descending. You can sort by multiple columns: ORDER BY country ASC, age DESC.
What does LIMIT do in SQL?
LIMIT restricts the number of rows returned. SELECT * FROM users LIMIT 10 returns at most 10 rows. Combined with ORDER BY, LIMIT answers "top N" questions. LIMIT without ORDER BY is meaningless.
What is OFFSET and how does it work with LIMIT?
OFFSET skips the first N rows before returning results. LIMIT 10 OFFSET 20 returns rows 21-30 (page 3 at 10 per page). Together, LIMIT and OFFSET implement pagination. But OFFSET on large tables is slow — the database has to count through all skipped rows.
What is an alias in SQL?
An alias renames a column or table in the output, using AS. SELECT name AS user_name renames the column. SELECT price * quantity AS total creates a computed column. Aliases only affect the output — the actual column name in the database doesn't change.
What is the difference between LIMIT and TOP?
Same concept, different syntax. LIMIT is PostgreSQL/MySQL/SQLite. TOP is SQL Server. Oracle uses FETCH FIRST N ROWS ONLY. Use whichever your database supports.
Can I ORDER BY a column not in SELECT?
Yes. You can ORDER BY any column in the table, even if it's not in the SELECT list. SELECT name FROM users ORDER BY age works — the output has only name, but rows are sorted by age.
How do I sort NULLs in SQL?
NULL sorting depends on the database. PostgreSQL: NULLs sort last in ASC. MySQL: NULLs sort first in ASC. To control it: ORDER BY age ASC NULLS LAST (PostgreSQL, Oracle). The portable trick: ORDER BY (age IS NULL) ASC, age ASC.
How do I paginate with SQL safely?
OFFSET pagination is simple but slow on large datasets. For tables with millions of rows, use keyset pagination: instead of LIMIT 10 OFFSET 10000, use WHERE id > 12345 ORDER BY id LIMIT 10. The database jumps directly to the position via the index.