SELECT min, max, open, close (trades)

[dadane]

I have a table (trades) with some stock-trades

It has the following fields
id (autogenerate, key)
stock (int)
tradetime (datetime)
price (decimal (10,4))
stockcount (int)

Now it would like to make an SELECT from 'trades' that will give me
min (the smallest price value)
max (the largest price value)
open (the first price value, - i.e. the price for the trade with smallest tradetime and should there be more smallest id)
close (the last, - like open but opposite)

GROUP BY stock, LEFT(tradetime,10)
WHERE "some where clause that will select fewer dates and/or stocks, - I'll will vary (but generally include a stock = N and I don't think I'll get into troubles with this part)

MIN and MAX is quite simple
But how do I do open and close?

[dadane]

Actually even better would be to get the weighted average if there are more trades at the same date and time, i.e. (count1*price1 + count2*price2 + count3*price3 ...) / (count1+count2+count3+...)

But it's not that important if it is much harder to do and/or put a much bigger stress on the database

[r937]

SELECT daytradedata.stock
     , daytradedata.tradedate
     , daytradedata.opentime
     , opentrade.price  AS openprice
     , daytradedata.closetime
     , closetrade.price  AS closeprice
  FROM ( SELECT stock                      
              , DATE(tradetime) AS tradedate
              , MIN(price) AS minprice
              , MAX(price) AS maxprice
              , MIN(tradetime)  AS opentime             
              , MAX(tradetime)  AS closetime      
           FROM trades       
          WHERE stock = 937           
            AND tradetime >= '2008-04-01'
            AND tradetime  < '2008-05-01' 
         GROUP
             BY stock                      
              , DATE(tradetime)  ) AS daytradedata
INNER
  JOIN trades AS opentrade
    ON opentrade.stock = daytradedata.stock
   AND opentrade.tradetime = daytradedata.opentime
INNER
  JOIN trades AS closetrade
    ON closetrade.stock = daytradedata.stock
   AND closetrade.tradetime = daytradedata.closetime

[dadane]

Thank you, - you're the man!