Re: select & group by

Michael L. Hostbaek wrote:
> I've got a problem selecting some specific data from my table. Imagine
> the following rows:
> 
> part    | mfg    | qty    | price    | eta    
> ---------------------------------------
> TEST1     ABC     10     100        (No ETA, as item is in stock)
> TEST1     ABC     12     120     04/04
> TEST2     CBA     17     10     05/05
> TEST2     CBA     10     20        (No ETA, as item is in stock)
> 
> 
> I'd like my selection to produce the following result:
> 
> part    | mfg    | qty    | qty incoming    | highest price    | eta
> -------------------------------------------------------------
> TEST1     ABC     10     12         120         04/04
> TEST2     CBA     10     17         20         05/05
> 
> Any clues on how to do this ? I kow the group by part, mfg, max(price) -
> but I do not know how to deal with the splitting up qty and stock qty
> and incoming qty.

How about something like:

SELECT  aa.part,  aa.mfg,  aa.qty,  bb.qty AS qty_incoming,  CASE WHEN aa.price > bb.price THEN aa.price ELSE bb.price
ENDAS 
 
highest_price,  aa.eta
FROM (   SELECT part,mfg,qty,price FROM mytable WHERE eta IS NOT NULL ) aa, (   SELECT part,mfg,qty,price FROM mytable
WHEREeta IS NULL ) bb
 
WHERE  aa.part = bb.part  AND aa.mfg=bb.mfg
;

This is assuming you only have one row with "eta" set for each 
(part,mfg). If not, you'll have to identify which row you want.

--   Richard Huxton  Archonet Ltd