pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to re-sort a sorted query?
6+ messages / 3 participants
[nested] [flat]
* How to re-sort a sorted query?
@ 2004-10-26 19:23 Yudie <yudie@axiontech.com>
0 siblings, 1 reply; 6+ messages in thread
From: Yudie @ 2004-10-26 19:23 UTC (permalink / raw)
To: pgsql-sql
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"
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?
Thanks..
Yudie
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to re-sort a sorted query?
@ 2004-10-26 20:34 Oliver Elphick <olly@lfix.co.uk>
parent: Yudie <yudie@axiontech.com>
0 siblings, 2 replies; 6+ messages in thread
From: Oliver Elphick @ 2004-10-26 20:34 UTC (permalink / raw)
To: Yudie <yudie@axiontech.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to re-sort a sorted query?
@ 2004-10-26 21:32 Yudie <yudie@axiontech.com>
parent: Oliver Elphick <olly@lfix.co.uk>
1 sibling, 0 replies; 6+ messages in thread
From: Yudie @ 2004-10-26 21:32 UTC (permalink / raw)
To: pgsql-sql
Oliver ,sorry, I didn't submit all complete fields as my example,
The reason I didn't use agregate function because I still need to select
another fields from storeproduct table and some outer joins.
What about if the data in storeproduct table shows like this:
ItemSku , StoreNumber , Price, Condition
==========================
10001 , 7 , 30.00, Used
10001 , 7 , 35.00, New <-- duplicate store number
10001 , 5 , 45.00, New
10001 , 2 , 50.00, New
However, should I use temporary table to make it simple? what about the
performance?
Yudie
----- Original Message -----
From: "Oliver Elphick" <olly@lfix.co.uk>
To: "Yudie" <yudie@axiontech.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, October 26, 2004 3:34 PM
Subject: Re: [SQL] How to re-sort a sorted query?
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to re-sort a sorted query?
@ 2004-10-26 21:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oliver Elphick <olly@lfix.co.uk>
1 sibling, 2 replies; 6+ messages in thread
From: Tom Lane @ 2004-10-26 21:43 UTC (permalink / raw)
To: olly@lfix.co.uk; +Cc: Yudie <yudie@axiontech.com>; 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'
order by storenumber, price) ss
order by price;
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to re-sort a sorted query?
@ 2004-10-26 21:57 Oliver Elphick <olly@lfix.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Oliver Elphick @ 2004-10-26 21:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Yudie <yudie@axiontech.com>; pgsql-sql
On Tue, 2004-10-26 at 17:43 -0400, Tom Lane wrote:
> 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.
Ah - because ORDER BY is applied before DISTINCT ON; I hadn't realised
that.
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to re-sort a sorted query?
@ 2004-10-27 15:30 Yudie <yudie@axiontech.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Yudie @ 2004-10-27 15:30 UTC (permalink / raw)
To: pgsql-sql
> 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
Thanks tom, this is working. I never thought it could be done like this.
yudie
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-10-27 15:30 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-26 19:23 How to re-sort a sorted query? Yudie <yudie@axiontech.com>
2004-10-26 20:34 ` Oliver Elphick <olly@lfix.co.uk>
2004-10-26 21:32 ` Yudie <yudie@axiontech.com>
2004-10-26 21:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-26 21:57 ` Oliver Elphick <olly@lfix.co.uk>
2004-10-27 15:30 ` Yudie <yudie@axiontech.com>
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