Lesson 4 of 20

In progress

Course progress

SQL

ORDER BY and LIMIT

Sort by several columns, control NULL placement, and paginate without repeating or skipping rows.

A database returns rows in no particular order. ORDER BY is how you make the order part of the answer, and LIMIT is how you take a slice of it.

What you will learn

  • Sort by one or several columns, ascending and descending.
  • Place NULLs deliberately.
  • Paginate with a stable order.

Sorting one column

SQL
Editable. Runs in your browser.





Press Run to see the output.

ASC is the default and DESC reverses it. The sort applies to the whole result, not to a page, so it runs before LIMIT.

Sorting by several columns

SQL
Editable. Runs in your browser.





Press Run to see the output.

The second column breaks ties in the first. This matters for pagination: if two rows share a score, their relative order is undefined without a second key.

The safe pattern is to end with a unique column:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Now the order is total: no two rows are ambiguous, so pages cannot overlap.

NULL placement

SQLite sorts NULLs before all other values in ascending order, so ORDER BY score puts the missing score first and DESC puts it last. PostgreSQL defaults to the opposite for ASC, and both support NULLS FIRST and NULLS LAST where the syntax is available.

SQL
Editable. Runs in your browser.





Press Run to see the output.

score IS NULL is 0 for real values and 1 for nulls, so sorting by it first pushes NULL rows to the end in every engine. That expression is portable, which is why it appears in real code.

Sorting by an expression

SQL
Editable. Runs in your browser.





Press Run to see the output.

The sort key can be any expression, including a CASE that encodes a custom priority. Sorting by a column you do not return is allowed and often useful.

Aliases work here even though they do not in WHERE, because ORDER BY is evaluated after the SELECT list:

SQL
Editable. Runs in your browser.





Press Run to see the output.

LIMIT and OFFSET

SQL
Editable. Runs in your browser.





Press Run to see the output.

LIMIT n returns at most n rows. OFFSET m skips m rows first.

SQL
Editable. Runs in your browser.





Press Run to see the output.

Page one is offset 0, page two is offset 2 for a page size of 2. This works and it has two costs: the database still processes the skipped rows, so deep pages get slower, and concurrent inserts can shift rows between pages. A keyset pagination approach avoids both:

SELECT id, player, score
FROM scores
WHERE score < :last_score
ORDER BY score DESC, id
LIMIT 20;

It requires a stable sort key and is the standard solution for large lists.

LIMIT without ORDER BY

SQL
Editable. Runs in your browser.





Press Run to see the output.

This returns three rows, not "the top three". Which three is up to the engine and can change after an insert or an index change. Any query where "top" matters needs ORDER BY.

The same applies to "latest": LIMIT 1 without ordering by a date is not the latest row, it is an arbitrary row.

In an API this is a correctness bug rather than a performance detail. A feed that shows "the latest five" without a sort key will look fine in development, where the newest row happens to be last, and then show yesterday’s news in production after a bulk import. Add the sort key, and add the unique tiebreaker with it.

A worked example

Suppose you want the three most recent games where a score exists, with the highest score first among the same day:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Read the clauses as a pipeline: filter out unknown scores, sort by day then score then id, take three. Each step narrows or orders the previous one.

Paginating with LIMIT/OFFSET and no unique tiebreaker. Two rows with the same score can swap between requests, so a user sees one row twice and never sees another.

Return the player with the highest score, and separately the two earliest games by date with ties broken by player name.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

The first query excludes NULL scores so "highest" means a real number, and orders by id as a tiebreaker. The second sorts by date first, then name, so games on the same day come out deterministically.

Recap

  • ORDER BY defines the result order; without it, row order is meaningless.
  • Add a unique tiebreaker whenever you paginate or take a top-N.
  • Use score IS NULL as a portable way to control where NULLs land.

Next, you will look closely at NULL and types, the two sources of most quiet SQL bugs.

Can I ORDER BY a column I did not select?
Yes in most databases, including SQLite. It is useful when the sort key is not part of the output.
Does ORDER BY use an index?
Often, if the index matches the sort order and the query’s filters. A mismatched direction or a computed key forces a sort step.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.