reference
Rank Dense Rank in SQL
Use rank dense rank when the query intent matches its syntax, row-grain behavior, and PostgreSQL edge cases. This reference gives a minimal example, realistic example, output, mistakes, and next lessons.
Syntax signature
Rank dense rank solves a specific SQL task. Use it only when its row behavior matches the output contract.
Syntax signature: function(...) OVER (PARTITION BY ... ORDER BY ... frame)
SELECT id, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY event_at, id) AS row_num FROM events;What it does and when to use it
Use rank dense rank when the query task needs this construct's specific row behavior, not because a keyword looks familiar.
Choose it only after naming the input grain, output grain, and whether filtering, grouping, ordering, or mutation semantics happen before or after the construct.
Plan about 10 minutes to read the syntax, compare the examples, and follow the related lesson link before practicing in the app.
Minimal example
The minimal example shows the smallest useful shape before dataset-specific details are added.
SELECT id, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY event_at, id) AS row_num FROM events;Realistic example and output
This example is small enough to audit by eye and mirrors the style used by the SQL Trail checker.
SELECT customer_id, order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS order_rank FROM orders ORDER BY customer_id, order_rank;| evidence | meaning |
|---|---|
| grain | one row per requested entity |
| edge case | nulls, ties, or duplicates stay explicit |
Behavior notes
Null behavior: decide whether rank dense rank keeps, removes, groups, compares, or ignores NULL values before trusting the visible output.
Type behavior: check whether rank dense rank preserves the expected data type or needs an explicit cast in PostgreSQL.
Ordering behavior: add deterministic ORDER BY or tie-breaker columns whenever rank dense rank can otherwise produce unstable presentation.
Cardinality behavior: confirm whether rank dense rank preserves one row per input, multiplies rows, collapses rows, or returns one row per group.
PostgreSQL-specific notes are linked to authoritative PostgreSQL documentation when syntax or semantics are dialect-specific.
Common errors and related resources
Learners often use rank dense rank correctly on the visible rows but miss null policy, tie handling, or the difference between filtering before and after aggregation.
When a PostgreSQL behavior is dialect-specific, SQL Trail links to the official documentation instead of inventing portability claims.