ItemSku , StoreNumber , Price<= /DIV>

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D

10001 , 7 , 30.00

10001 , 7 , 35.00 <-- duplicate store n= umber=20

10001 , 5 , 45.00

10001 , 2 ,=20 50.00

Then I do this query to get unique store number and also the cheapest = price=20 from each store:

SQL=3D "Select distinct on (storenumber), itemsku, storenumber,price

from storeproduct where itemsku=3D'10001'

order by storenumber, price"

Result #1:

ItemSku , StoreNumber ,=20 Price

<= FONT=20 face=3DArial size=3D2>

10001 , 2 ,=20 50.00

10001 , 5 , 45.00

10001 , 7 ,=20 30.00

The question is how to make the query that returns as above = but=20 sorted by price?

Thanks..

Yudie

------=_NextPart_000_002D_01C4BB67.5DAF1F40-- From olly@lfix.co.uk Tue Oct 26 20:35:04 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EA5BB3A3C44 for ; Tue, 26 Oct 2004 21:35:04 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30535-01 for ; Tue, 26 Oct 2004 20:34:53 +0000 (GMT) Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by svr1.postgresql.org (Postfix) with ESMTP id 065AC3A44AC for ; Tue, 26 Oct 2004 21:34:49 +0100 (BST) Received: from lfix.demon.co.uk ([80.177.205.209] helo=cerberus.lfix.co.uk) by anchor-post-33.mail.demon.net with esmtp (Exim 4.42) id 1CMY2C-000PCG-By; Tue, 26 Oct 2004 20:35:12 +0000 Received: from linda.lfix.co.uk ([192.168.1.1]) by cerberus.lfix.co.uk with esmtp (Exim 4.34) id 1CMY1h-0005eO-T8; Tue, 26 Oct 2004 21:34:41 +0100 Subject: Re: How to re-sort a sorted query? From: Oliver Elphick Reply-To: olly@lfix.co.uk To: Yudie Cc: pgsql-sql@postgresql.org In-Reply-To: <003001c4bb91$4c6647c0$a101a8c0@home.axiontech.com> References: <003001c4bb91$4c6647c0$a101a8c0@home.axiontech.com> Content-Type: text/plain Organization: LFIX Limited Date: Tue, 26 Oct 2004 21:34:41 +0100 Message-Id: <1098822881.2414.42.camel@linda> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 7bit X-LFIX-MailScanner: Found to be clean X-MailScanner-From: olly@lfix.co.uk X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/188 X-Sequence-Number: 19291 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 From yudie@axiontech.com Tue Oct 26 21:34:21 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 69B9B3A44CC for ; Tue, 26 Oct 2004 22:34:21 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52488-04 for ; Tue, 26 Oct 2004 21:34:10 +0000 (GMT) Received: from axiontech.com (unknown [216.52.172.78]) by svr1.postgresql.org (Postfix) with SMTP id 177153A44BB for ; Tue, 26 Oct 2004 22:34:11 +0100 (BST) Received: (qmail 814 invoked from network); 26 Oct 2004 21:34:23 -0000 Received: from pc-00161 (HELO yudie) (192.168.1.161) by mail.axiontech.com (192.168.1.6) with SMTP; 26 Oct 2004 21:34:23 -0000 Message-ID: <004d01c4bba3$5d992f00$a101a8c0@home.axiontech.com> From: "Yudie" To: References: <003001c4bb91$4c6647c0$a101a8c0@home.axiontech.com> <1098822881.2414.42.camel@linda> Subject: Re: How to re-sort a sorted query? Date: Tue, 26 Oct 2004 16:32:57 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/191 X-Sequence-Number: 19294 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" To: "Yudie" Cc: 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 From tgl@sss.pgh.pa.us Tue Oct 26 21:46:50 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 60E293A3D39 for ; Tue, 26 Oct 2004 22:46:50 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56765-06 for ; Tue, 26 Oct 2004 21:46:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 650933A2BC1 for ; Tue, 26 Oct 2004 22:46:40 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9QLhfxs013740; Tue, 26 Oct 2004 17:43:41 -0400 (EDT) To: olly@lfix.co.uk Cc: Yudie , 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 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 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/192 X-Sequence-Number: 19295 Oliver Elphick 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 From olly@lfix.co.uk Tue Oct 26 21:58:12 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 54BA63A44D6 for ; Tue, 26 Oct 2004 22:58:12 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59955-08 for ; Tue, 26 Oct 2004 21:58:01 +0000 (GMT) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id ADBB93A44D5 for ; Tue, 26 Oct 2004 22:58:03 +0100 (BST) Received: from lfix.demon.co.uk ([80.177.205.209] helo=cerberus.lfix.co.uk) by anchor-post-34.mail.demon.net with esmtp (Exim 4.42) id 1CMZKM-0004Jn-EC; Tue, 26 Oct 2004 21:58:02 +0000 Received: from linda.lfix.co.uk ([192.168.1.1]) by cerberus.lfix.co.uk with esmtp (Exim 4.34) id 1CMZKE-0005zL-6w; Tue, 26 Oct 2004 22:57:54 +0100 Subject: Re: How to re-sort a sorted query? From: Oliver Elphick Reply-To: olly@lfix.co.uk To: Tom Lane Cc: Yudie , pgsql-sql@postgresql.org In-Reply-To: <13739.1098827021@sss.pgh.pa.us> References: <003001c4bb91$4c6647c0$a101a8c0@home.axiontech.com> <1098822881.2414.42.camel@linda> <13739.1098827021@sss.pgh.pa.us> Content-Type: text/plain Organization: LFIX Limited Date: Tue, 26 Oct 2004 22:57:53 +0100 Message-Id: <1098827873.2414.47.camel@linda> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 7bit X-LFIX-MailScanner: Found to be clean X-MailScanner-From: olly@lfix.co.uk X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/193 X-Sequence-Number: 19296 On Tue, 2004-10-26 at 17:43 -0400, Tom Lane wrote: > Oliver Elphick 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 From yudie@axiontech.com Wed Oct 27 15:31:32 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5D0F23A3CBA for ; Wed, 27 Oct 2004 16:31:32 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 95870-08 for ; Wed, 27 Oct 2004 15:31:26 +0000 (GMT) Received: from axiontech.com (unknown [216.52.172.78]) by svr1.postgresql.org (Postfix) with SMTP id 674AB3A4677 for ; Wed, 27 Oct 2004 16:31:25 +0100 (BST) Received: (qmail 13081 invoked from network); 27 Oct 2004 15:31:35 -0000 Received: from pc-00161 (HELO yudie) (192.168.1.161) by mail.axiontech.com (192.168.1.6) with SMTP; 27 Oct 2004 15:31:35 -0000 Message-ID: <003001c4bc39$dd0470b0$a101a8c0@home.axiontech.com> From: "Yudie" To: References: <003001c4bb91$4c6647c0$a101a8c0@home.axiontech.com> <1098822881.2414.42.camel@linda> <13739.1098827021@sss.pgh.pa.us> Subject: Re: How to re-sort a sorted query? Date: Wed, 27 Oct 2004 10:30:15 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/198 X-Sequence-Number: 19301 > 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 >