beginner 6 min read

Sorting and Limiting Results

Written with a little help from AI, reviewed and edited by a human.

So far when we’ve used SELECT, the results come back in whatever order the database decides. But often you want results in a specific order — like showing posts from newest to oldest, or students sorted alphabetically by name. You might also want to show only the first 10 results instead of all of them.

That’s what ORDER BY and LIMIT are for.

Real life example

Imagine you have a stack of student test papers on your desk, and the principal asks you three questions:

  1. “Show me the papers sorted by score, highest first” → You sort the entire stack from 92 down to 65.
  2. “Show me just the top 3 scores” → You take the sorted stack and hand over only the first 3 papers.
  3. “Show me scores 4 through 6” → You skip the first 3 and give the next 3 papers.

That’s exactly what ORDER BY and LIMIT do — they let you control how the data is sorted and how many rows to return.

ORDER BY — Sorting results

Let’s say we have a table of students with their scores:

idnamescore
1Alice85
2Bob92
3Charlie78
4Diana88
5Eve92

By default, if you do SELECT * FROM students, they come back in whatever order they were inserted (usually by id). But what if you want them sorted by score?

SELECT * FROM students
ORDER BY score;

Result:

idnamescore
3Charlie78
1Alice85
4Diana88
2Bob92
5Eve92

Now they’re sorted by score from lowest to highest. This is the default direction, called ascending order (ASC).

If you want highest to lowest instead, use DESC (descending):

SELECT * FROM students
ORDER BY score DESC;

Result:

idnamescore
2Bob92
5Eve92
4Diana88
1Alice85
3Charlie78

Now the highest scores are at the top.

You can use ASC explicitly if you want, but it’s optional since ascending is the default. Most people only write DESC when they need descending order.

Sorting by text

You can also sort by text columns like names:

SELECT * FROM students
ORDER BY name;

Result:

idnamescore
1Alice85
2Bob92
3Charlie78
4Diana88
5Eve92

Alphabetical order, just like sorting words in a dictionary.

Sorting by multiple columns

What if two students have the same score? You can add a second sort:

SELECT * FROM students
ORDER BY score DESC, name ASC;

This says: “Sort by score highest first. If two students have the same score, sort those by name alphabetically.”

Result:

idnamescore
2Bob92
5Eve92
4Diana88
1Alice85
3Charlie78

Bob and Eve both have 92, but Bob comes first because “Bob” comes before “Eve” alphabetically.

LIMIT — Showing only some results

Sometimes you don’t want all the rows — you just want the first few. That’s what LIMIT does.

SELECT * FROM students
ORDER BY score DESC
LIMIT 3;

Result:

idnamescore
2Bob92
5Eve92
4Diana88

This gives you only the top 3 students by score. Notice we sorted first (ORDER BY score DESC) and then limited the results. Without the sorting, LIMIT 3 would just give you the first 3 rows in whatever order they were stored.

Always use ORDER BY before LIMIT if you care about which rows you get. Otherwise you’re just grabbing random rows.

OFFSET — Skipping rows

OFFSET lets you skip a certain number of rows before you start returning results. This is useful for pagination — like showing page 2 of results.

SELECT * FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 2;

This says: “Sort by score, skip the first 2 rows, then show the next 3.”

Result:

idnamescore
4Diana88
1Alice85
3Charlie78

We skipped Bob and Eve (the top 2) and got the next 3 students.

Real life pagination example

Think of a website showing 10 blog posts per page:

  • Page 1: LIMIT 10 OFFSET 0 (show posts 1-10)
  • Page 2: LIMIT 10 OFFSET 10 (skip the first 10, show posts 11-20)
  • Page 3: LIMIT 10 OFFSET 20 (skip the first 20, show posts 21-30)

That’s how most websites implement pagination behind the scenes.

Combining everything

You can use all of these together:

SELECT name, score
FROM students
WHERE score >= 80
ORDER BY score DESC
LIMIT 2;

This says:

  1. Get only students with a score of 80 or higher
  2. Sort them by score, highest first
  3. Show only the top 2

Result:

namescore
Bob92
Eve92

When to use what

  • Use ORDER BY when you need results in a specific order (alphabetical, newest first, highest to lowest, etc.)
  • Use LIMIT when you only want a few rows (top 10, first 5, etc.)
  • Use OFFSET with LIMIT when you need pagination (page 2, page 3, etc.)

The order always goes: WHERE → ORDER BY → LIMIT → OFFSET. You can’t put LIMIT before ORDER BY or WHERE after LIMIT — SQL has strict rules about the order of keywords.

Share: