Core · DatabasesLesson 2 of 6Free to read

Sorting and limiting · SQL and Databases

How do you sort, limit and tidy a result?

Why you need it

A page of search results is a sorted, limited list: the ten cheapest flights, the newest posts first. Without ORDER BY a database may return rows in any order it likes, and the order can change between runs.

The intuition

Think of the query as an assembly line, in this order:

FROM picks the table
WHERE throws rows away
SELECT picks columns and computes new ones
ORDER BY sorts what is left
LIMIT keeps the first few

AS gives a computed column a name, and DISTINCT removes repeated rows.

Definition

ORDER BY

Sorts rows by one or more columns, ascending (`ASC`, the default) or descending (`DESC`). Ties are broken by the next column listed.

LIMIT

Returns at most this many rows. `LIMIT 3 OFFSET 3` skips three rows first, which is how page 2 of a list works.

Examples

Top 3 by price

Run it in the lesson

Sort from most to least expensive, then keep three.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Output

name | price
Backpack | 25.0
Lamp | 18.0
Mug | 6.0

Step by step

L3DESC: biggest first. Backpack 25.0, Lamp 18.0, Mug 6.0, …
L4Stop after three rows.Backpack, Lamp, Mug

Computed columns and DISTINCT

Run it in the lesson

price * 1.2 is computed for each row and named with AS. DISTINCT turns five categories into three.

SELECT name, price * 2 AS two_of_them
FROM products
WHERE category = 'home';

SELECT DISTINCT category FROM products ORDER BY category;

Output

name | two_of_them
Lamp | 36.0
Mug | 12.0

category
bags
home
paper

Step by step

L1The new column only exists in this result, not in the table.Lamp 36.0, Mug 12.0
L5bags, home, paper: each once, in alphabetical order.

Common mistakes

Trusting the default order

Rows come back in whatever order is fastest for the database. If the order matters, write ORDER BY, every time.

LIMIT before sorting

LIMIT always runs last. ORDER BY price LIMIT 1 is the cheapest product of all, not the first row sorted.

Quick quiz

1. What does this print?

SELECT name FROM customers ORDER BY name DESC LIMIT 2;

A
```
name
Eli
Dana
```
B
```
name
Ada
Bo
```
C
```
name
Dana
Eli
```

Show the answer

Answer: A

DESC sorts Z to A: Eli, Dana, Chen, Bo, Ada. LIMIT keeps the first two.

2. What does this print?

SELECT DISTINCT day FROM orders WHERE qty < 3 ORDER BY day;

A
```
day
2025-03-02
2025-03-03
2025-03-05
```

day
2025-03-02
2025-03-03
2025-03-05
2025-03-05

C
```
day
2025-03-05
```

Show the answer

Answer: A

Orders 3, 4, 5 and 6 have qty under 3. Two of them share 2025-03-05, and DISTINCT keeps it once.

Practice

3 exercises in three steps: predict what a program prints, change a program to meet a new requirement, then write one from scratch. Your code runs in the browser and is checked the moment you submit.

PredictSort by two columns
Type exactly what this query returns.
ModifyNewest first
Change the query so it returns the 3 most recent orders, newest first. Keep the same columns.
WritePrice with tax
Return every product's name and its price with 10% tax as a column named with_tax (price × 1.1), cheapest first.

Practise this lesson

Open the interactive lesson to edit and run these examples and solve the exercises with instant feedback. Free to start, no sign-up.

Open the interactive lesson Course map

Interactive lessons open in order. If you have not done the earlier ones yet, you start at the course map.

Terms in this lesson

Term	Chinese	Definition
Ascending (ASC)	升序	From small to large, A to Z, old to new.
Descending (DESC)	降序	From large to small, Z to A, new to old.
Alias (AS)	别名	A new name for a column or table, valid inside one query.