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.

row_number_order.sql 
CREATE TABLE scores (name TEXT, points INTEGER);
INSERT INTO scores VALUES ('Ada', 8), ('Lin', 12), ('Mia', 9);
WITH params(min_points) AS (VALUES ()) 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 ()) 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 ()) SELECT name, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rank FROM scores WHERE points >= (SELECT min_points FROM params) ORDER BY rank;
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.