To: olly@lfix.co.uk
Cc: Yudie <yudie@axiontech.com>, pgsql-sql@postgresql.org
Subject: Re: How to re-sort a sorted query? 
In-reply-to: <1098822881.2414.42.camel@linda> 
References: <003001c4bb91$4c6647c0$a101a8c0@home.axiontech.com>
	<1098822881.2414.42.camel@linda>
Comments: In-reply-to Oliver Elphick <olly@lfix.co.uk>
	message dated "Tue, 26 Oct 2004 21:34:41 +0100"
Date: Tue, 26 Oct 2004 17:43:41 -0400
Message-ID: <13739.1098827021@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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.

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