Practical technology learning
SQL Ranking: Decide What a Tie Means Before Choosing a Window Function
A rank is a rule applied to a defined ordering, not a judgement supplied by the database. This exercise uses three fictional records to compare ties and row positions. No student marks, company rankings or real performance records are involved.
Write down the input and question
Create A with score 90, B with score 90 and C with score 80. Ask two separate questions: which score group is each record in, and in what repeatable order should individual rows appear? Keeping those questions separate avoids accidentally treating an ID-based presentation order as a better score.
Compare gap and no-gap ranking
For RANK() OVER (ORDER BY score DESC), the two 90 records are peers and both receive 1; the 80 record receives 3. DENSE_RANK with the same ordering produces 1,1,2. Explain the difference using the tied group and the following rank, not simply a memorized function name.
Give individual row positions their own rule
ROW_NUMBER gives each row its own position. An ordering such as score descending followed by a suitable unique record ID can make the order between equal-score records repeatable. That is useful for row positions, but it does not create a meaningful performance difference between equal scores.
Inspect the peer definition after edits
Peer groups depend on every expression in the window ORDER BY. If you add a unique ID to the RANK ordering, the formerly equal-score rows no longer share the entire ordering key. Keep score-based ranking and deterministic row-number ordering distinct when the report needs both.
Review a small result table
- For the score-only RANK window, expect 1,1,3.
- For the score-only DENSE_RANK window, expect 1,1,2.
- For ROW_NUMBER, state the tie-breaker and why it is suitable.
- Specify a final display order separately when the report requires it.
Preserve the reporting decision
Save the fictional input, window definitions and expected results together. Window calculations preserve individual rows rather than collapsing them into aggregate groups. This practice supports further querying in the Mango Engineers SQL learning path and makes the report easier to review.