ORACLE sql: Return columns associated with MAX value of a subquery that SUM values -

ORACLE sql: Return columns associated with MAX value of a subquery that SUM values -

i'm having issues problem i'm supposed utilize subqueries. have find names of authors of book(s) have been sold most. have works if there 1 row matches max value of totalsold.

select fname, lname books b bring together bookauthor ba on b.isbn = ba.isbn bring together author on ba.authorid = a.authorid b.isbn = (select isbn (select sum(quantity) totalsold, ba.isbn isbn bookauthor ba bring together author on ba.authorid = a.authorid bring together orderitems oi on ba.isbn = oi.isbn grouping ba.isbn order totalsold desc) rownum = 1)

however in theory there tie max value , stuck on how homecoming associated isbn tied max value(s).

in essence how query without using rownum = 1.

i came seems on clunky works:

select fname, lname books b bring together bookauthor ba on b.isbn = ba.isbn bring together author on ba.authorid = a.authorid b.isbn in (select isbn (select sum(quantity) totalsold, ba.isbn isbn bookauthor ba bring together author on ba.authorid = a.authorid bring together orderitems oi on ba.isbn = oi.isbn grouping ba.isbn) totalsold = (select max(totalsold) (select sum(quantity) totalsold, ba.isbn isbn bookauthor ba bring together author on ba.authorid = a.authorid bring together orderitems oi on ba.isbn = oi.isbn grouping ba.isbn)))

your solution wrong.

you need firstly find number, represents max sold books. query homecoming 1 number. $maxnumber then need find books sold same number $maxnumber then find authors joining books step 2

i'll give subquery homecoming set of isbn(s) of book sold max copies.

select oi2.isbn orderitems oi2 sum(oi2.quantity) = (select max(st.sumtotal) total (select sum(oi.quantity) sumtotal orderitems oi grouping oi.isbn) st) grouping oi2.isbn

now can bring together or utilize subquery in(subquery) function. need grouping author_id, because on author can have 2 books sold same max copies.

depending on final requirement, need build different queries in case want author's name or authors' name names of books.

sql oracle