ROW_NUMBER, RANK, DENSE_RANK in PostgreSQL

Introduction

These functions help you number or rank rows in a table based on some order. They make it easy to find positions or ranks of items.

When you want to assign a unique number to each row in a sorted list, like numbering students by their scores.

When you want to find the rank of items where ties get the same rank but skip numbers after, like ranking runners in a race.

When you want to rank items with ties but without skipping numbers, like ranking salespeople with equal sales.

When you need to paginate results and want to number rows for each page.

When you want to compare positions of items within groups, like ranking employees by department.

Syntax

PostgreSQL

ROW_NUMBER() OVER (ORDER BY column1 [ASC|DESC])
RANK() OVER (ORDER BY column1 [ASC|DESC])
DENSE_RANK() OVER (ORDER BY column1 [ASC|DESC])

These are window functions used with the OVER() clause.

ORDER BY inside OVER() defines how rows are sorted before numbering or ranking.

Examples

Assigns a unique row number to players ordered by score from highest to lowest.

PostgreSQL

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num FROM players;

Ranks players by score. Players with the same score get the same rank, but ranks skip numbers after ties.

PostgreSQL

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM players;

Ranks players by score. Players with the same score get the same rank, and ranks do not skip numbers after ties.

PostgreSQL

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM players;

Sample Program

This example creates a small table of players with scores. It then shows how each function numbers or ranks the players ordered by score descending.

PostgreSQL

CREATE TEMP TABLE players (name TEXT, score INT);
INSERT INTO players VALUES
('Alice', 90),
('Bob', 95),
('Charlie', 90),
('David', 85);

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number,
  RANK() OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players
ORDER BY score DESC, name;

Important Notes

ROW_NUMBER always gives a unique number, even if values tie.

RANK skips numbers after ties, so ranks can jump (e.g., 1, 2, 2, 4).

DENSE_RANK does not skip numbers after ties (e.g., 1, 2, 2, 3).

Summary

Use ROW_NUMBER to give each row a unique position.

Use RANK to assign ranks with gaps after ties.

Use DENSE_RANK to assign ranks without gaps after ties.