CODEWITHSUNDEEP
sqloracle-databasejoinsubquery
ReeDfence
ReeDfence

Reputation: 41

How do I make my query avoid the "single-row subquery returns more than one row" error

I need to return the titles of books that are most frequently purchased. If multiple books are purchased the same amount then I receive the error:

SELECT title
FROM books JOIN orderitems
USING (isbn) WHERE isbn=
    (SELECT isbn
    FROM orderitems
    HAVING SUM (quantity) =
        (SELECT MAX(SUM(quantity))
        FROM orderitems
        GROUP BY isbn)
    GROUP BY isbn)
GROUP BY title;

Upvotes: 0

Views: 239

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want to rank the books by order quantity and only show the book(s) ranked highest (rank #1 for the greatest quantity) . There is the window function RANK for ranking rows.

You want to select the book title only, so select the title from books and apply your criteria in a WHERE clause.

select title
from books
where (isbn, 1) in -- ISBN ranked #1
(
  select isbn, rank() over (order by sum(quantity) desc)
  from orderitems
  group by isbn
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use window functions!

SELECT title
FROM (SELECT isbn, title, SUM(quantity) as quantity,
             RANK() OVER (ORDER BY SUM(quantity) DESC) as seqnum
      FROM books b JOIN
           orderitems oi
           USING (isbn)
      GROUP BY isbn, title
     ) t
WHERE seqnum = 1;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

you sub-query return multiple row which not support = operator so i used in

SELECT title
FROM books JOIN orderitems
USING (isbn) WHERE isbn in
    (SELECT isbn
    FROM orderitems
    group by isbn
    HAVING SUM (quantity) =
        (SELECT MAX(SUM(quantity))
        FROM orderitems
        GROUP BY isbn
        )
    )

Upvotes: 1

Related Questions