From: Nis Jørgensen <nis@superlativ.dk>
To: pgsql-sql@postgresql.org
Subject: Re: The nested view from hell - Restricting a subquerry
Date: Mon, 23 Jul 2007 13:57:11 +0200
Message-ID: <f8252s$g8p$1@sea.gmane.org> (raw)
In-Reply-To: <87d4yjmkiw.fsf@oxford.xeocode.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nis@superlativ.dk
Subject: Re: The nested view from hell - Restricting a subquerry
In-Reply-To: <f8252s$g8p$1@sea.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox