This is the seventh day of my participation in the August More text Challenge. For details, see:August is more challenging

❤ ️ the original ❤ ️

Write an SQL query to implement the score ranking.

If two scores are the same, the two scores are ranked the same. Note that the next place after a split should be the next consecutive integer value. In other words, there should be no “gap” between the rankings.

+ - + -- -- -- -- -- -- -- + | | Id Score | + - + -- -- -- -- -- -- -- + | 1 | | 3.50 3.65 | | 2 | 3 | | 4.00 | | | | 3.85 4 5 4.00 | | | | | 3.65 6  | +----+-------+Copy the code

For example, given the Scores table above, your query should return (in descending order of Scores) :

+ -- -- -- -- -- -- - + -- -- -- -- -- -- + | Score | Rank | + -- -- -- -- -- -- - + -- -- -- -- -- - + 4.00 | 1 | | | | 1 | 4.00 3.85 2 | | | | | 3 | 3.65 3.65 | | 3 | | 3.50 4 | | + -- -- -- -- -- - + -- -- -- -- -- - +Copy the code

⭐️ ⭐️

Obviously, this is a ranking problem, and There are four ranking functions in Oracle:

  • The rank function
  • Dense_rank function
  • Row_number function
  • Ntile function

Here’s an example of what these four functions can do:

create table scores
(   id number(6)
   ,score number(4.2));insert into scores values(1.3.50);
insert into scores values(2.3.65);
insert into scores values(3.4.00);
insert into scores values(4.3.85);
insert into scores values(5.4.00);
insert into scores values(6.3.65);
commit;

select
    id 
   ,score
   ,rank(a)over(order by score desc) rank               -- Ranking according to performance, pure ranking
   ,dense_rank(a)over(order by score desc) dense_rank   -- Rankings are based on grades, and the same grades are ranked the same
   ,row_number(a)over(order by score desc) row_number   -- Ranking in order of performance
   ,ntile(3) over (order by score desc) ntile         -- Grades are divided according to scores
from scores;
Copy the code

If the two scores are the same,RANK 和 DENSE_RANKThe scores of the functions rank the same, butRANKThere will be “gaps” between the positions of functions, soDENSE_RANKThe function is the solution to the problem.

☀️

select score as "Score",dense_rank(a)over (order by score desc) as "Rank" from scores;
Copy the code

Go to LeetCode and check out the results:

❄️ at the end ❄️

If you know the rownum function, you can use the rownum function to solve the problem. If you don’t know the rownum function, you can use the rownum function.

That’s the end of this sharing

If you think the article is helpful to you, like, collect, follow, comment, one key four support, your support is the biggest motivation for my creation.