Watch the tutorial
Watch the full SQL ranking tutorial or the short introduction.
Two people share second place. Does the next person finish third or fourth? In SQL, the answer depends on the ranking function you choose. That choice can change a leaderboard or a top courses report even when the input data is the same.
We will run a small PostgreSQL example, look at the rows, and then rank courses within each category. The data below is synthetic: it does not change any database tables. You can run each complete SQL block in pgAdmin's Query Tool or another PostgreSQL client.
Four scores, one tie
| ID | Participant | Score |
|---|---|---|
| 1 | Anna | 100 |
| 2 | Ben | 90 |
| 3 | Chris | 90 |
| 4 | Dana | 80 |
Ben and Chris have the same score. This self-contained query shows all three rankings side by side:
WITH scores (id, participant, score) AS (
VALUES (1, 'Anna', 100), (2, 'Ben', 90),
(3, 'Chris', 90), (4, 'Dana', 80)
)
SELECT participant, score,
ROW_NUMBER() OVER (ORDER BY score DESC, id) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM scores
ORDER BY score DESC, id;
WITH ... VALUES supplies a temporary set of input rows for this query; it does not create a table. The OVER clause determines how each window function orders its input. The final ORDER BY controls the order in which the query displays the result.
| participant | score | row_num | rank_num | dense_rank_num |
|---|---|---|---|---|
| Anna | 100 | 1 | 1 | 1 |
| Ben | 90 | 2 | 2 | 2 |
| Chris | 90 | 3 | 2 | 2 |
| Dana | 80 | 4 | 4 | 3 |
ROW_NUMBER: one sequence number per row
ROW_NUMBER() assigns a different position to each row. Both 90-point rows still represent a tie by score, but their row numbers must differ. Here, id is a unique tie-breaker: Ben has a smaller ID, so he receives 2. If we ordered by score alone, PostgreSQL would not promise which tied row gets which number. This is useful when the requirement is to select a fixed number of rows and a deterministic tie-breaker is acceptable.
RANK: equal scores share a rank; gaps remain
RANK() assigns 2 to both 90-point rows. Since two rows have already occupied positions 2 and 3, the next rank is 4. Choose it when equal values should share a rank and the gap reflects the number of preceding rows.
DENSE_RANK: equal scores share a rank; no gaps
DENSE_RANK() also assigns 2 to both 90-point rows, but the next distinct score receives 3. Choose it when you want consecutive ranks for distinct score levels.
The distinction comes from what counts as a peer. The ordering inside RANK() and DENSE_RANK() uses score alone, so the two 90s are peers. Adding a unique id to those two window orderings would make them different peers and eliminate the intended tie. We use id in the ROW_NUMBER() ordering, and separately in the final display ordering; that final sort does not change the ranks. PostgreSQL documents these functions in Window Functions.
Rank courses separately in each category
Suppose a training company wants the two highest ranks in each category, including every course tied at second place. The next example supplies seven fictional courses; the values are an illustration, not counts from a live TrainingHub database.
WITH courses (course_id, category, course_name, enrollments) AS (
VALUES (1, 'Java', 'Java Basics', 80),
(2, 'Java', 'Spring Intro', 70),
(3, 'Java', 'Java Testing', 70),
(4, 'SQL', 'SQL Basics', 100),
(5, 'SQL', 'SQL Joins', 90),
(6, 'SQL', 'SQL Reports', 90),
(7, 'SQL', 'SQL Views', 80)
), ranked AS (
SELECT course_id, category, course_name, enrollments,
ROW_NUMBER() OVER (
PARTITION BY category ORDER BY enrollments DESC, course_id
) AS row_num,
RANK() OVER (
PARTITION BY category ORDER BY enrollments DESC
) AS course_rank
FROM courses
)
SELECT category, course_name, enrollments, row_num, course_rank
FROM ranked
WHERE course_rank <= 2
ORDER BY category, enrollments DESC, course_id;
PARTITION BY category starts a separate ranking for Java and SQL. The inner CTE calculates ranks for each course; the outer query filters them. The SQL result is:
| category | course_name | enrollments | row_num | course_rank |
|---|---|---|---|---|
| Java | Java Basics | 80 | 1 | 1 |
| Java | Spring Intro | 70 | 2 | 2 |
| Java | Java Testing | 70 | 3 | 2 |
| SQL | SQL Basics | 100 | 1 | 1 |
| SQL | SQL Joins | 90 | 2 | 2 |
| SQL | SQL Reports | 90 | 3 | 2 |
Each category returns three courses, not two: both courses tied for second place satisfy course_rank <= 2. If the requirement is at most two rows per category, use WHERE row_num <= 2 instead. For this fixture that selects Java Basics, Spring Intro, SQL Basics and SQL Joins. It intentionally excludes one tied course in each category using course_id as the tie-breaker. If a category has fewer than two courses, it returns only the rows available.
DENSE_RANK() <= 2 would select the first two distinct enrollment-count levels per category. It can return more than two rows as well. More generally, RANK() <= N and DENSE_RANK() <= N need not return the same set because RANK can have gaps. Write the business rule first: exactly N rows where available, ranks with gaps, or N distinct value levels.
Common mistakes to avoid
- Confusing display order with rank order. The final
ORDER BYsorts the output. TheORDER BYinside eachOVERdefines that function's ranking. - Accidentally removing ties. A unique identifier inside
RANKorDENSE_RANKmakes rows with equal scores non-peers. Use an ID only where it is part of the intended ranking rule. - Assuming
ROW_NUMBER()is reproducible with ties. Provide an explicit, unique tie-breaker if a stable choice matters. - Filtering a window value in the same query's
WHERE. Calculate it in a CTE or subquery, then filter in the outer query. See the PostgreSQL window-function tutorial. - Ignoring missing values. These examples use non-null scores. If real data can contain
NULL, decide where those rows belong and specify an appropriateNULLS FIRSTorNULLS LASTrule. Do not treat a missing score as zero without a reason.
Try it yourself
Change Dana's score to 90. Predict all three columns before running the first query again. Which function gives three rows the same rank? Which one continues without a gap? For another exercise, change the second query to return at most two rows per category and verify which tied course is excluded.
For more practice with PostgreSQL, SQL and a synthetic training-business database, explore Practical SQL with PostgreSQL: From Queries to Real Projects. This is the instructor's referral link. The example above runs independently of the paid course.
No comments:
Post a Comment