Oracle SQL using sub queries

Question

I'm having some troubles learning sub queries. I'm trying to show the most expensive book that each customer has ever ordered and doing it with a uncorrelated sub query.

Here is what I have so far:

select Firstname || ', '|| Lastname "Name", title, retail
from Customers join orders using (Customer#) join orderitems using (Order#)                 
join books using (Isbn)
where retail =ANY (select max(retail)
                from books join orderitems using (isbn) join Orders using (order#) join Customers using (Customer#)
                group by Customer#)                 
order by  Firstname, Lastname;

The inner query shows the max retail of each customer but I'm not sure why with the outer query it shows customers multiple times. I've just started learning SQL and any help would be greatly appreciated

Answer 1

select Firstname || ', '|| Lastname "Name", title, retail
from Customers join orders using (Customer#) join orderitems using (Order#)                 
join books using (Isbn)
where (Customer#, retail) in (select Customer#,  max(retail)
                from books join orderitems using (isbn) join Orders using (order#) join Customers using (Customer#)
                group by Customer#)                 
order by  Firstname, Lastname;

or the way I like to do that, without selecting the same thing twice:

select Name, title, Customer#, retail
from (select Firstname || ', '|| Lastname "Name", title, Customer#,  
             retail, max(retail) over (partition by  Customer#) max_retail
        from books 
        join orderitems using (isbn) 
        join Orders using (order#) 
        join Customers using (Customer#))    
where retail=max_retail             
order by  Firstname, Lastname;