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

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'
orderby storenumber, price) ss
 
order by price;
        regards, tom lane