A query's results come back in no particular guaranteed order unless you ask for one. ORDER BY sets that order, and LIMIT caps how many rows you get back, useful for things like "top 10 highest earners" or showing one page of results at a time.
Learning Objectives
- Sort query results ascending or descending, on one or more columns.
- Limit a query to a specific number of rows.
- Use LIMIT and OFFSET together to page through results.
ORDER BY
ASC (ascending, low to high) is the default if you don't specify. DESC sorts descending, high to low. You can sort by multiple columns, separated by commas, the second column only matters for breaking ties in the first. ORDER BY itself is identical across every SQL database.
SELECT first_name, salary FROM employees ORDER BY salary DESC; -- Result: -- first_name | salary -- Grace | 98000 -- Ada | 95000 -- Margaret | 87000
Sorting by Multiple Columns
This groups rows by department alphabetically, and within each department, sorts highest salary first.
SELECT first_name, department, salary FROM employees ORDER BY department ASC, salary DESC;
LIMIT and OFFSET
LIMIT caps the number of rows returned. OFFSET skips a number of rows before starting to return results, the pagination example above returns rows 21 through 30, page 3 if each page shows 10 rows. Always pair LIMIT/OFFSET (or SQL Server's OFFSET/FETCH NEXT) with an ORDER BY, without one, which rows you get on each "page" isn't guaranteed to stay consistent.
SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 2; -- Result: -- first_name | salary -- Grace | 98000 -- Ada | 95000
SELECT first_name, salary FROM employees ORDER BY salary DESC OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY; -- Same result as the LIMIT 2 example
SELECT first_name FROM employees ORDER BY id LIMIT 10 OFFSET 20;
SELECT first_name FROM employees ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Limiting Rows Across Databases
| Database | Syntax |
|---|---|
| SQLite / MySQL / PostgreSQL | LIMIT 10 OFFSET 20 |
| SQL Server | OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY |
| Oracle (12c+) | OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY |
This is one of the few places standard SQL syntax genuinely splits by database. LIMIT/OFFSET, which most of this course uses, covers SQLite, MySQL, and PostgreSQL, the great majority of real-world use, SQL Server and recent Oracle use OFFSET/FETCH NEXT instead, shown in the code examples above.
Frequently Asked Questions
What happens if I use LIMIT without ORDER BY?
You'll get some number of rows back, but which ones is not guaranteed, the database is free to return them in whatever order is most efficient internally, and that order can even change between runs of the same query.
Is OFFSET slow for paging through a lot of data?
For large offsets it can be, the database still has to scan through and discard all the skipped rows first. For a data set with millions of rows and deep pagination, a technique called keyset pagination (filtering by "WHERE id > last_seen_id" instead of OFFSET) performs much better, but that's beyond what a beginner needs to worry about yet.
Can I sort by a column I'm not selecting?
Yes, same as WHERE, ORDER BY works on the table's columns directly, not just the ones listed in SELECT.
Common Beginner Mistakes
Using LIMIT/OFFSET for pagination without an ORDER BY
It usually still works while testing, since a small table often happens to come back in insertion order, then breaks in production once the table is large enough that the database stops returning rows in a predictable order, rows going missing or repeating between pages.
Assuming LIMIT works unchanged on SQL Server
SQL Server doesn't support LIMIT at all, it uses OFFSET ... ROWS FETCH NEXT ... ROWS ONLY instead, and that syntax also requires an ORDER BY to be present, LIMIT does not.
Expecting ORDER BY department, salary DESC to sort both columns descending
ASC/DESC applies only to the column it's written next to. This sorts department ascending (the default) and salary descending, not both descending, write ORDER BY department DESC, salary DESC if that's actually the intent.
Using large OFFSET values for deep pagination without expecting a slowdown
OFFSET doesn't skip rows for free, the database still scans and discards every row before the offset. Page 1,000 of a million-row table, scanned this way, is measurably slower than page 1.
Best Practices
- Always pair LIMIT/OFFSET (or SQL Server's OFFSET/FETCH NEXT) with an explicit ORDER BY, never rely on a database's incidental default ordering.
- Sort by a column with mostly-unique values (like id) as a tiebreaker when the main sort column can repeat, so page boundaries stay stable.
- Be explicit about ASC/DESC on every column in a multi-column ORDER BY rather than assuming one setting applies to all of them.
- For deep pagination over large tables, reach for keyset pagination (WHERE id > last_seen_id) instead of a large OFFSET once performance actually matters.
Interview Questions
Write a query returning the 3rd and 4th highest-paid employees, by salary.
SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2; — ORDER BY ranks everyone by salary descending, OFFSET 2 skips the top two, and LIMIT 2 takes the next two.
How would you write that same query for SQL Server?
SELECT first_name, salary FROM employees ORDER BY salary DESC OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY; — SQL Server has no LIMIT keyword, OFFSET/FETCH NEXT does the equivalent job and requires the ORDER BY to be present.
Why is OFFSET-based pagination considered inefficient at scale, and what's the alternative?
The database can't jump straight to row 1,000,000, it has to scan and discard every row before the offset, so cost grows with how deep you page in. Keyset pagination replaces OFFSET with a WHERE condition on the last row seen, for example WHERE id > 1000000 ORDER BY id LIMIT 10, which lets the database jump straight there using an index instead of scanning from the start.
If two rows have the exact same salary, is their relative order in an ORDER BY salary DESC query guaranteed?
No. ORDER BY only guarantees order for the column(s) you specify, rows that tie on salary can come back in any order relative to each other, and that order isn't guaranteed to stay the same between runs. Add a second ORDER BY column, like id, to make the order fully deterministic.
Summary
This lesson covered ORDER BY for sorting results ascending or descending across one or more columns, LIMIT for capping how many rows come back, and OFFSET for skipping rows to build pagination, including why SQL Server needs OFFSET/FETCH NEXT instead of LIMIT.
That completes Module 1, SQL Basics: what SQL is and how it differs from a specific database product, getting a database running, and the core SELECT / WHERE / ORDER BY / LIMIT toolkit for reading data back out of a table.
What's Next?
Module 2, Working With Data, is coming next: writing data with INSERT, UPDATE, and DELETE, handling NULL correctly, and introducing aggregate functions, GROUP BY, joins, and subqueries. Check the course page for progress as each lesson goes live.