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" <[EMAIL PROTECTED]> To: "Yudie" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
