sql / expert
Snippet
Ranking Distributions with DENSE_RANK
Window functions for ranking allow for sophisticated ordering logic. While RANK() skips numbers if ties occur, DENSE_RANK() ensures a continuous sequence. This distinction is critical in statistical analysis and competitive scoring systems.
snippet.sql
sql
1
2
3
4
SELECT student_name, score,RANK() OVER (ORDER BY score DESC) as rank_gap,DENSE_RANK() OVER (ORDER BY score DESC) as rank_denseFROM exam_results;
Breakdown
1
RANK() OVER (ORDER BY score DESC)
Assigns a rank; identical scores get the same rank, and the next rank is skipped.
2
DENSE_RANK() OVER (ORDER BY score DESC)
Assigns a rank; identical scores get the same rank, but the next rank is the immediate integer.