sql - Select first record with aggregate functions -

i have thermometer starts logging data every morning whenever machine turns on.

i select min, max, , average temperatures, temperatures when machine turns on , off every day.

my table structure follows:

time logged, date logged, temperature

i group date logged aggregates day, can't seem find way select temperature @ first , last time stamps recorded.

any help?

you want use windows function this:

select t.datelogged, min(t.temperature), max(t.temperature), avg(t.temperature),        max(case when t.seqnum_asc = 1 t.temperature end) firsttemperature,        max(case when t.seqnum_desc = 1 t.temperature end) lasttemperature, (select t.*,              row_number() on (partition datelogged order timelogged) seqnum_asc,              row_number() on (partition datelogged order timelogged desc) seqnum_desc       t      ) t group t.datelogged order datelogged 

what doing adding 2 new variables. 1 enumerates values during day starting @ 1 first reading (seqnum_asc). other enumeration has 1for last reading (seqnum_desc`).

to values, conditional summation used.

if like, can pretty same thing using min() , max() window fucnctions, rather row_number():

select t.datelogged, min(t.temperature), max(t.temperature), avg(t.temperature),        max(case when timelogged = mintime t.temperature end) firsttemperature,        max(case when timelogged = maxtime t.temperature end) lasttemperature, (select t.*,              min(timelogged) on (partition datelogged) mintime,              max(timelogged) on (partition datelogged) maxtime        t       ) t group t.datelogged order datelogged