Skip site navigation (1) Skip section navigation (2)

Re: How to re-sort a sorted query?

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Yudie <yudie(at)axiontech(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: How to re-sort a sorted query?
Date: 2004-10-26 21:57:53
Message-ID: 1098827873.2414.47.camel@linda (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
On Tue, 2004-10-26 at 17:43 -0400, Tom Lane wrote:
> Oliver Elphick <olly(at)lfix(dot)co(dot)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

Oliver Elphick                                          olly(at)lfix(dot)co(dot)uk
Isle of Wight                    
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 

In response to

pgsql-sql by date

Next:From: Scott PederickDate: 2004-10-27 11:02:35
Subject: JOIN not being calculated correctly
Previous:From: Tom LaneDate: 2004-10-26 21:43:41
Subject: Re: How to re-sort a sorted query?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group