What does the error "single-row subquery returns more than one row" means and how to fix i-CodePudding

I'm trying to query for all movies that have a higher score than a randomly selected movie score. I don't understand the error to fix my query.

List the titles of movies that have a higher score than some movie in the database.
(Don't hardcode "some" movie as any specific movie such as "Titanic".)

SELECT TITLE FROM MOVIE
WHERE SCORE > (SELECT SCORE FROM MOVIE);

CodePudding user response：

The sub-query SELECT SCORE FROM MOVIE does not return a random score of a movie in other words a scalar but a list of scores. You can't compare a scalar to the left of the > to a list at its right (the result of the sub-query). This is syntactically wrong. You can correct this in many different ways. It depends on what you want. Here are some working examples with > operator:

SELECT TITLE FROM MOVIE
WHERE SCORE > (SELECT MAX(SCORE) FROM MOVIE)

SELECT TITLE FROM MOVIE
WHERE SCORE > (SELECT MIN(SCORE) FROM MOVIE)

SELECT TITLE FROM MOVIE
WHERE SCORE > ALL (SELECT SCORE FROM MOVIE

SELECT TITLE FROM MOVIE
WHERE SCORE > ANY (SELECT SCORE FROM MOVIE)

CodePudding user response：

As @Halim Saad-Rached mentions in their answer, SELECT SCORE FROM MOVIE is returning multiple rows and you are then trying to compare each row in the outer query to multiple rows in the inner query.

I'm trying to query for all movies that have a higher score than a randomly selected movie score.

You need to find a single random movie rather than all movies.

From Oracle 12, you can use:

SELECT TITLE
FROM   MOVIE
WHERE  SCORE > (SELECT SCORE
                FROM   MOVIE
                ORDER BY DBMS_RANDOM.VALUE()
                FETCH FIRST ROW ONLY
               );

Or, in earlier versions:

SELECT TITLE
FROM   MOVIE
WHERE  SCORE > (SELECT score
                FROM   (
                  SELECT SCORE
                  FROM   MOVIE
                  ORDER BY DBMS_RANDOM.VALUE()
                )
                WHERE  ROWNUM = 1
               );

or, using analytic functions to only query the table once:

SELECT title
FROM   (
  SELECT title,
         score,
         MIN(score)
           KEEP (DENSE_RANK FIRST ORDER BY DBMS_RANDOM.VALUE(), ROWNUM)
           OVER ()
           AS random_score
  FROM   MOVIE
)
WHERE  score > random_score;

db<>fiddle here