3 ms·
Am curious. How would you write it 'properly'? Other (good) alternative is with toporders as ( select max(order_total) as maxOT, customer_id fro
by zasdffaa 4y ago
Am curious. How would you write it 'properly'? Other (good) alternative is
with toporders as (
select max(order_total) as maxOT, customer_id
from orders
group by customer_id
)
select last_name, maxOT
from customers join toporders
on ...
What would you suggest?
- datalopers 4y agoCorrelated subqueries run N times, once for each outer row. Your solution (assuming the database supports predicate pushdown) is far better.
- dragonwriter 4y agoSELECT customer.first_name, customer.last_name, max(order.order_total) as maximum_order FROM customers customer INNER JOIN orders order ON (customer.id = order.customer_id) GROUP BY customer.id
- zasdffaa 4y agoI don't think subqueries are so bad - at least they're clear, and if fast, that's surely problem solved. IME they are clearer and often faster, so overall better. I know purists don't like them but I do.
- deleted 4y ago[deleted]