Advanced

Putting rows in order

I read a table and assumed the rows came back in the order I added them. They do not. A table is a bag of rows, and if you want an order you have to ask for one.

What you will see

id 6 5 4 sqlite>

About 2 min of reading

Try It

Sort the same table up and down, then cap it to the top rows.

Sort by id ascending. The rows come back smallest id first.

SELECT id FROM users ORDER BY id;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

id 1 2 3 4 5 6 sqlite>

Sort descending. Now the largest id is on top.

SELECT id FROM users ORDER BY id DESC;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

id 6 5 4 3 2 1 sqlite>

Sort descending and take only three. You get the three largest ids.

SELECT id FROM users ORDER BY id DESC LIMIT 3;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

id 6 5 4 sqlite>

Data Flow

FROM reads rows, ORDER BY sorts them, and LIMIT keeps only the first few of the sorted list.

1 FROM reads the rows

↓

2 ORDER BY sorts them

↓

3 LIMIT cuts the list

↓

4 The top rows return

Why this works

Tables have no order

A table is a set of rows, not a list. Nothing in a database promises that rows come back in insertion order, and as soon as you filter or join, the order can shuffle. If the order matters to you, ORDER BY is where you state it. If you skip it, you are accepting whatever order the database happens to produce.

ORDER BY then LIMIT

The clauses run in a fixed order, and that order is what makes LIMIT mean what you think. The rows are sorted by ORDER BY first, and only then does LIMIT cut the list. So ORDER BY id DESC LIMIT 2 is the two biggest, not the first two added. Write them in that order and the meaning follows.

Examples

Sort one column

SELECT name FROM users ORDER BY id;

ORDER BY goes after FROM and sorts the rows by a column. Numbers sort by value, text sorts alphabetically. The answer comes back in that order every time.

Sort the other way

SELECT name FROM users ORDER BY id DESC;

DESC means descending, biggest first. The default is ascending. This is how you find the newest or largest rows without reading them all.

Take only the top few

SELECT name FROM users ORDER BY id DESC LIMIT 2;

LIMIT caps how many rows come back. ORDER BY first, then LIMIT, so you get the two largest. Order and limit together are how you ask for the top N.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Counting instead of listing

Previous Lesson

Counting instead of listing

Next Lesson

Counting per group

Counting per group →