Description

Login
Back

Beginner · 12 min

Sorting and limiting results

Choose a deterministic order before taking a subset.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Break ties explicitly

ORDER BY supports several keys. A unique tie-breaker makes results predictable when durations match. DESC sorts a key from largest to smallest, while ASC is the default direction.

SELECT id, minutes FROM sessions ORDER BY minutes DESC, id ASC LIMIT 3;

Output

id | minutes
3 | 30
5 | 25
1 | 20

Skip a small number of rows

LIMIT bounds the returned rows and OFFSET skips rows in that ordering. Without a stable order, pages can overlap or change unexpectedly. Large offsets can become expensive; later lessons introduce cursor-style filtering.

SELECT id FROM sessions ORDER BY id LIMIT 2 OFFSET 2;

Output

id
3
4

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return the two longest sessions as ID and minutes. Break ties by ascending ID.

Show solution
SELECT id, minutes FROM sessions ORDER BY minutes DESC, id LIMIT 2;

The ordering determines which two rows survive the limit.

Practice