What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All three number rows within an ordered partition. They differ only in how they treat ties.
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM results;
| name | score | ROW_NUMBER |
RANK |
DENSE_RANK |
|---|---|---|---|---|
| Ann | 100 | 1 | 1 | 1 |
| Bob | 90 | 2 | 2 | 2 |
| Cy | 90 | 3 | 2 | 2 |
| Dee | 80 | 4 | 4 | 3 |
ROW_NUMBER: always unique, ties are split arbitrarily unless the ORDER BY has a tie-breaker.RANK: ties share a rank and the next rank skips (1, 2, 2, 4), like sports standings.Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.