Description

Login
Back

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

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 each session with its position within its member, longest first and ID as the tie-breaker.

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.

Practice