순위 함수

SELECT 칼럼1,칼럼2 --- ROW_NUM() OVER (PARTITION BY 그룹핑할칼럼 ORDER 정렬기준칼럼 정렬기준) AS 지칭할 칼럼명

PARTITION BY =구간을 나눠서 순위를 매길 수 있게 되는 것

​ORDER BY = 파티션 내에서 뭐를 기준으로 정렬 할건지, 이거에 따라 RANK 값이 매겨짐

ex) OVER (ORDER BY score desc;) as rank = 점수가 제일 큰값을 1순위로 매겨라 

'ROW_NUMBER() OVER (PARTITION BY 파티션기준칼럼 ORDER BY 정렬기준칼럼 정렬기준)'

 

ROW_NUMBER() -단순 번호 넘기기

123456

RANK

123336

DENSE_RANK

123345

 

---

 

문제출처

Rank Scores - LeetCode

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+

 

Write a solution to find the rank of the scores. The ranking should be calculated according to the following rules:

• The scores should be ranked from the highest to the lowest.
• If there is a tie between two scores, both should have the same ranking.
• After a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no holes between ranks.

Return the result table ordered by score in descending order.

Output: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

정답

SELECT

  score,

  DENSE_RANK() OVER (ORDER BY Score DESC) AS rank

FROM Scores

ORDER BY score DESC;  

오답노트

RANK 함수 내 ORDER BY에 ; 붙이지마