On Tue, 2004-10-26 at 17:43 -0400, Tom Lane wrote: > Oliver Elphick <[EMAIL PROTECTED]> 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 [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
