BeginnerCoder
DonateLesson 19 — Ranking with a subquery
Ranking without window functions
A rank can be computed with a correlated subquery: rank = 1 + how many rows have a higher score. Ties get the same rank automatically, and the next rank skips (1, 2, 2, 4) — this is called "competition ranking".
SELECT runner,
1 + (SELECT COUNT(*) FROM races r2 WHERE r2.secs < r.secs) AS place
FROM races r
Why it matters: it shows how ranking really works, and it runs on old databases without window functions.
Fill in the subquery condition: count rows with more points than the current row. Expected: Bo and Cy share rank 2.
Run your code — when the output matches the task, you will see a green check. Then mark the lesson complete.
Test your knowledgeA quick 3-question quiz on this lesson — instant feedback.
Ready for more? Optional paid resources that pick up where this lesson leaves off.
Some links on this page are affiliate links: we may earn a commission at no extra cost to you. As an Amazon Associate I earn from qualifying purchases.
Project-based SQL Deep Dive courses if you want a structured paid path after the free lessons. Wait for a sale — prices drop often.
Browse on Udemy →Search Amazon for SQL Deep Dive books — check the publication year so the examples match current versions.
Search Amazon →