Re: The nested view from hell - Restricting a subquerry

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        1001        2
100

Step 3 producesorder_id     max(invoice_id)    sum(mileage)--------------------------------------------1        2
200
 

Step 4 eliminates this row, since it does not satisfy the criteria.


Nis