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