Re: GROUP BY question

Dan Star <danstar_at_engman-taylor.com> wrote: select Whse,CustPN,Price,Notes from random_table;
: Whse CustPN Price Notes
: 208 34512 6.50 called to expedite on 4/2
: 209 34512 7.50
: 210 34512 7.00
: 208 55555 2.00
: 209 55555 1.50 last receiving on 4/6 was damaged
 

: is there a way to use SQL to GROUP BY CustPN MAX(Price) and return

select TRUNC(AVG(Whse),0),CustPN,MAX(Price) from random_table

	GROUP BY CustPN
	ORDER BY CustPN DESC;



:    208    55555     2.00


:    209    34512     7.50

That works, btw, at least in Oracle. But TRUNC(AVG(Whse),0) is probably not what you want, semantically. I guess you are trying to get the max price per customer, and the wherehouse they got it from? If you think about it, that's two sets, so you need a self-join (I use a subselect):

select r2.whse,r1.cust_pn,r1.max_price
from (select Cust_PN,MAX(Price) as max_price from random_table GROUP BY Cust_PN) r1,

         random_table r2
WHERE r2.cust_pn = r1.cust_pn and r1.max_price = r2.price Received on Thu Apr 19 2001 - 02:44:55 CEST