Re: How to re-sort a sorted query? - Mailing list pgsql-sql

From Oliver Elphick
Subject Re: How to re-sort a sorted query?
Date
Msg-id 1098827873.2414.47.camel@linda
Whole thread Raw
In response to Re: How to re-sort a sorted query?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
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
adulterousand sinful generation; of him     also shall the Son of man be ashamed, when he cometh      in the glory of
hisFather with the holy angels."                                     Mark 8:38 
 



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: How to re-sort a sorted query?
Next
From: "Dmitry P. Ovechkin"
Date:
Subject: RULE and default nextval() column