From: Oliver Elphick <olly@lfix.co.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Yudie <yudie@axiontech.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to re-sort a sorted query?
Date: Tue, 26 Oct 2004 22:57:53 +0100
Message-ID: <1098827873.2414.47.camel@linda> (raw)
In-Reply-To: <13739.1098827021@sss.pgh.pa.us>
References: <003001c4bb91$4c6647c0$a101a8c0@home.axiontech.com>
<1098822881.2414.42.camel@linda>
<13739.1098827021@sss.pgh.pa.us>
On Tue, 2004-10-26 at 17:43 -0400, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > On Tue, 2004-10-26 at 14:23 -0500, Yudie wrote:
> >> Then I do this query to get unique store number and also the cheapest
> >> price from each store:
> >>
> >> SQL= "Select distinct on (storenumber), itemsku, storenumber,price
> >> from storeproduct where itemsku='10001'
> >> order by storenumber, price"
>
> > That won't get you the cheapest price,
>
> Sure it will. It's a perfectly good application of DISTINCT ON.
> However, he has to use that particular ORDER BY to get the answers
> he wants.
Ah - because ORDER BY is applied before DISTINCT ON; I hadn't realised
that.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Whosoever therefore shall be ashamed of me and of my
words in this adulterous and sinful generation; of him
also shall the Son of man be ashamed, when he cometh
in the glory of his Father with the holy angels."
Mark 8:38
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: olly@lfix.co.uk, tgl@sss.pgh.pa.us, yudie@axiontech.com
Subject: Re: How to re-sort a sorted query?
In-Reply-To: <1098827873.2414.47.camel@linda>
* 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