Question 20606 in Mysql Question posted in
The official MySQL documentation can be found here.

"Subquery returns more than 1 row" in SQL (From Leetcode 178. Rank Scores) – Mysql

I am solving the SQL problem from Leetcode: 178. Rank Scores..

The question of the leetcode is this:

Write an SQL query to rank 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.
The query result format is in the following example.

Input:

Scores table:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

Output:

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

Note: id is the primary key for this table.
Each row of this table contains the score of a game. Score is a floating point value with two decimal places.

I tried the below solution:

SELECT  s2.score,  
            (SELECT COUNT(DISTINCT s2.score) 
             FROM Scores s1                 
             WHERE s1.score >= s2.score) 'rank'
FROM Scores s2
ORDER BY score DESC

Then it prompts an error Subquery returns more than 1 row. However when I changed DISTINCT s2.score to DISTINCT s1.score, then it works and the solution is accepted.

I had been search this question for a long time and I did not get an answer to solve my doubt in this context. I wonder

  • how which syntax cause it returns more than 1 row?
  • Why change DISTINCT s2.score to DISTINCT s1.score solve this problem?

Any help is appreciated. Thanks.

Tags:

2

Answers


  1. 0

    In a "correlated subquery" you should only refer to the "outer table" (s2) in the where clause of that subquery. You cannot ALSO refer to s2 in the select clause of the subquery because s2 isn’t part of the from clause of that subquery.

    Imagine that you wrote this query:

    select s2.x from table1 as s1

    That will fail because s2 is not part of the from clause of that query. However it gets more complex, and confusing, in the your subquery because although s2 is a valid alias it will not work (as you expect) in the select clause of the subquery.

    Also: The error message you are getting occurs in MySQL 5.6 (and possibly other versions) but you would get a different message in MySQL regarding only_full_group_by. In my view neither error message really describes the actual error (that you have referenced the outer table in the select clause of a correlated subquery).

    When you do refer to s1 in the select clause of the subquery you now are referring to the data available via the subquery’s from clause, and hence it will run successfully. i.e.

    SELECT  s2.score,  
            (SELECT COUNT(distinct s1.score) 
             FROM Scores as s1                 
             WHERE s1.score >= s2.score) 'rank'
FROM `Scores` s2
ORDER BY score DESC;

    Produces:

    score   rank
4.00    1
4.00    1
3.85    2
3.65    3
3.65    3
3.50    4

    see: https://dbfiddle.uk/M1tUPqDD

    Hopefully this explains why you got the error, and why it works when you select from s1 instead of s2.

    Think of this portion being the core subquery:

                 SELECT COUNT(distinct s1.score) 
             FROM Scores as s1

    and then think of this portion as being how the "correlation" is created:

                 WHERE s1.score >= s2.score

    Only refer to any "outer tables" in that second portion.

    Login or Signup to reply.
  2. 0

    My result..

    select score,dense_rank() over (order by score desc ) r_rank from ranks

    https://dbfiddle.uk/Bp2w7WZ6

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search