Hacker News Reader

Comment by dragonwriter | Hacker News Reader

I’ve seen it a lot by older SQL devs or in enterprise places with ossified standard patterns. Lots of RDBMS’s historically had quirks and a lot of workarounds became cargo cult practices that often got culturally transmitted to the communities of other DBMSs and/or survived beyond the problem they were meant to workaround.

View on HN

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?

Correlated subqueries run N times, once for each outer row. Your solution (assuming the database supports predicate pushdown) is far better.

  SELECT 
    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

I 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.

Reply on news.ycombinator.com