Window Functions

Row Number

Ranking Ordered Rows

ROW_NUMBER assigns a position after rows are ordered. It is useful for top lists and stable rankings.

Program

Play the query to rank scores after a selectable minimum-points filter.

min_points
row_number_order.sql
Replay: real traced execution (multi-file project)
CREATE TABLE scores (name TEXT, points INTEGER);
INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);
WITH params(min_points) AS (VALUES (9)) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;

CREATE TABLE scores (name TEXT, points INTEGER);
INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);
WITH params(min_points) AS (VALUES (8)) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;

CREATE TABLE scores (name TEXT, points INTEGER);
INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);
WITH params(min_points) AS (VALUES (10)) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;

  1. scores ← 0 rows

    1CREATE TABLE scores (name TEXT, points INTEGER);2INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);
    values this step0 rowsscores

  2. scores ← 3 rows

    1CREATE TABLE scores (name TEXT, points INTEGER);2INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);3WITH params(min_points) AS (VALUES (9)) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;
    values this step3 rowsscores

  3. result ← 2 rows

    2INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);3WITH params(min_points) AS (VALUES (9)) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;
    values this step2 rowsresult

  1. scores ← 0 rows

    1CREATE TABLE scores (name TEXT, points INTEGER);2INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);
    values this step0 rowsscores

  2. scores ← 3 rows

    1CREATE TABLE scores (name TEXT, points INTEGER);2INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);3WITH params(min_points) AS (VALUES (8)) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;
    values this step3 rowsscores

  3. result ← 3 rows

    2INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);3WITH params(min_points) AS (VALUES (8)) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;
    values this step3 rowsresult

  1. scores ← 0 rows

    1CREATE TABLE scores (name TEXT, points INTEGER);2INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);
    values this step0 rowsscores

  2. scores ← 3 rows

    1CREATE TABLE scores (name TEXT, points INTEGER);2INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);3WITH params(min_points) AS (VALUES (10)) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;
    values this step3 rowsscores

  3. result ← 1 row

    2INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);3WITH params(min_points) AS (VALUES (10)) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;
    values this step1 rowresult

Follow the Rows

  1. The default min_points is 9.
  2. The inserted scores order by points as Lin 12, Mia 9, then Ada 8.
  3. The filter keeps Lin and Mia because their points are at least 9.
  4. ROW_NUMBER numbers the kept rows by points descending.
  5. The result is Lin rank 1, then Mia rank 2. | name | points | kept? | row number | | --- | --- | --- | --- | | Lin | 12 | yes | 1 | | Mia | 9 | yes | 2 | | Ada | 8 | no | - |
ROW_NUMBER `ROW_NUMBER() OVER (...)` numbers rows in the chosen order.
OVER `OVER (ORDER BY points DESC)` defines the window order.
parameter CTE `params` holds the selectable threshold used by the filter.

Exercise: row_number_order.sql

Reproduce the Lin and Mia ranks, then use the pinned min_points variants to predict why 8 adds Ada as rank 3 and 10 leaves only Lin.