Gregory Stark skrev: >> 1. Look up all order_ids for which (order_id,my_invoice_id) appear in >> eg_orders >> >> 2. Find all rows (in both branches of the UNION) with these id_s >> >> 3. Group the rows, and calculate max(invoice_id) >> >> 4. Filter the result rows on max(invoice_id) = my_invoice_id. > > So here's a hypothetical data set for which this algorithm fails: > > order_id invoice_id mileage > -------------------------------------------- > 1 1 100 > 1 2 100 > > Your algorithm would produce > > order_id max(invoice_id) sum(mileage) > -------------------------------------------- > 1 1 100 > > Whereas the correct output would be to output no records at all.
(I assume you are using "1" as the parameter to the query). You seem to have interpreted one or more of the steps differently than I intended them. I have tried to clarify (resorting to SQL for the first step). 1. SELECT DISTINCT order_id FROM eg_order WHERE invoice_id = my_invoice_id 2. Find all rows (in both branches of the UNION) with these order_ids. 3. Group the rows on order_id, and calculate max(invoice_id) 4. Filter the result rows on max(invoice_id) = my_invoice_id. Thus, step 1 produces order_id ---------- 1 step 2 produces order_id invoice_id mileage -------------------------------------------- 1 1 100 1 2 100 Step 3 produces order_id max(invoice_id) sum(mileage) -------------------------------------------- 1 2 200 Step 4 eliminates this row, since it does not satisfy the criteria. Nis ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
