How to write this SQL query more elegantly ( joining + max query )

Ok I am using the following example from w3school

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

and I want to get the date in which the amount was ordered

SELECT OrderDate 
FROM Orders
WHERE OrderID = (SELECT OrderID 
                 FROM OrderDetails 
                 WHERE Quantity = (SELECT MAX(Quantity)  
                                   FROM OrderDetails));

This works but my guts tell me I need to use joining or having ??

Answer

You want the date of the order that has the maximum quantity.

It does not look like you do need two levels of subqueries. You could use a row-limiting subquery instead:

select orderdate 
from orders
where orderid = (select orderid from from orderdetails order by quantity desc limit 1)

This is shorter, and does not fail if there is more than one order with the same, maximum quantity (while your original code does, because the subquery returns more than one row).

Another approach uses window functions:

select o.orderdate
from orders o
inner join (
    select od.*, rank() over(order by quantity desc) rn
    from orderdetails od
) od on od.orderid = o.orderid
where od.rn = 1

This will properly handle top ties, in the sense that it will return them all (while the first query returns only one of them).