To: pgsql-sql@postgresql.org
From: =?ISO-8859-1?Q?Nis_J=F8rgensen?= <nis@superlativ.dk>
Subject: Re: The nested view from hell - Restricting a subquerry
Date: Mon, 23 Jul 2007 13:57:11 +0200
Lines: 64
Message-ID: <f8252s$g8p$1@sea.gmane.org>
References: <46A2CABF.3080108@obviously.com>
	<f7v6na$2gc$1@sea.gmane.org>	<2385.1185127507@sss.pgh.pa.us>
	<f80r63$8lo$1@sea.gmane.org>	<87y7h74un7.fsf@oxford.xeocode.com>
	<f81ep0$emn$1@sea.gmane.org> <87d4yjmkiw.fsf@oxford.xeocode.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 1.5.0.12 (Windows/20070509)
In-Reply-To: <87d4yjmkiw.fsf@oxford.xeocode.com>

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