SQL · Advanced · 18 min
Ranking rows within groups
Use window functions while preserving individual rows.
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');Number rows per partition
ROW_NUMBER assigns a sequence within each partition. ORDER BY inside OVER controls that sequence; an outer ORDER BY controls display order. Include a tie-breaker when selecting a single top row.
SELECT member_id, id, ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY minutes DESC, id) AS position FROM sessions ORDER BY member_id, position;Output
member_id | id | position 1 | 1 | 1 1 | 2 | 2 2 | 3 | 1 2 | 4 | 2 3 | 5 | 1 3 | 6 | 2
Understand ranking ties
RANK gives tied values the same rank and leaves gaps after ties. DENSE_RANK also shares ranks but closes those gaps. Adding a unique tie-breaker inside the rank ordering would remove the ties.
SELECT id, minutes, RANK() OVER (ORDER BY minutes DESC) AS rank, DENSE_RANK() OVER (ORDER BY minutes DESC) AS dense_rank FROM sessions ORDER BY minutes DESC, id;Output
id | minutes | rank | dense_rank 3 | 30 | 1 | 1 5 | 25 | 2 | 2 1 | 20 | 3 | 3 4 | 15 | 4 | 4 6 | 15 | 4 | 4 2 | 10 | 6 | 5
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 each session with its position within its member, longest first and ID as the tie-breaker.
Restart the numbering for each member with PARTITION BY.
Show solution
SELECT member_id, id, ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY minutes DESC, id) AS position FROM sessions ORDER BY member_id, position;The partition keeps each member sequence independent.
Put it into practice
- Read the sample tables and predict the result before running the query.
- Solve the task, compare the returned rows, and explain why the solution works.