Leetcode 178. Rank Scores

Description

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks. [Read Full Description Here]

Solutions

Method 1 Thought Process

1. Finding the rank is the process of comparing the salary to all the other salaries in the table and a sub-query will enable us to do that as well as return the distinct count of salaries that is above the given salary.

Method 2 Thought Process (A better solution provided by another user on Leetcode)

1. Define two user variables and have them working as pointers/cursors with one pointing at the rank and the other pointing at the previous values.

2. Initializing rank = 0 and prev = -1 and the value returned from logical comparison between prev and current Score value will be either 0 or 1, and that will affect whether we would like to add 1 to the rank or not.

Key

1. Having a sub-query which take the variable in the outer select query as a constant value and compare that value with all values in the same table by iterating through it in the sub-query,

2. User variable can be really useful in a case like this. By assigning two 'pointers' we no longer need to execute a sub-query to loop within the same table.

#Database #Leetcode #SQL