From: Oliver Elphick <olly@lfix.co.uk>
To: Yudie <yudie@axiontech.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to re-sort a sorted query?
Date: Tue, 26 Oct 2004 21:34:41 +0100
Message-ID: <1098822881.2414.42.camel@linda> (raw)
In-Reply-To: <003001c4bb91$4c6647c0$a101a8c0@home.axiontech.com>
References: <003001c4bb91$4c6647c0$a101a8c0@home.axiontech.com>
On Tue, 2004-10-26 at 14:23 -0500, Yudie wrote:
> I have a query that need to be sorted in order of price of store's
> product with unique store number.
>
> Here is a sample data of storeproduct table:
>
>
> ItemSku , StoreNumber , Price
> ==========================
> 10001 , 7 , 30.00
> 10001 , 7 , 35.00 <-- duplicate store number
> 10001 , 5 , 45.00
> 10001 , 2 , 50.00
>
> 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, just an arbitrary one determined
by the physical storage order.
You need to use GROUP BY with an aggregate function:
SELECT itemsku, storenumber, MIN(price)
FROM storeproduct WHERE itemsku = '10001'
GROUP BY itemsku, storenumber
ORDER BY price, storenumber;
> Result #1:
> ItemSku , StoreNumber , Price
> 10001 , 2 , 50.00
> 10001 , 5 , 45.00
> 10001 , 7 , 30.00
>
> The question is how to make the query that returns as above but sorted
> by price?
The literal answer to your question is to put price first in the ORDER
BY clause, but I'm not convinced you actually want to know something
that simple.
--
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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olly@lfix.co.uk, yudie@axiontech.com
Subject: Re: How to re-sort a sorted query?
In-Reply-To: <1098822881.2414.42.camel@linda>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox