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

Re: How to re-sort a sorted query?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: olly(at)lfix(dot)co(dot)uk
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:43:41
Message-ID: 13739.1098827021@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
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.

So the only way (I think) to change the ordering for display is to
wrap this as a sub-select:

select * from
  (select distinct on (storenumber), itemsku, storenumber,price
   from storeproduct where itemsku='10001' 
   order by storenumber, price) ss
order by price;

			regards, tom lane

In response to

Responses

pgsql-sql by date

Next:From: Oliver ElphickDate: 2004-10-26 21:57:53
Subject: Re: How to re-sort a sorted query?
Previous:From: YudieDate: 2004-10-26 21:32:57
Subject: Re: How to re-sort a sorted query?

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