"sparks" <sp****@here.com> wrote in message
news:iq********************************@4ax.com...
I have 3 fields
id result date
1 1 11/11/02
1 2.3 12/12/03
1
1 4.6 06/04/04
can one query give me all these things?
the min result record 1 1 11/11/02
the first result based on date 1 1 11/11/02
the maximum result info. 1 4.6 06/04/04
This could be slow on large tables. A better way might be to turn the
aggregated list into a derived table and join it back to the original table
to get the related record info - post back if you need that one instead.
Otherwise this should work:
select r.id, min(r.result) as minResult,
(
select r2.[date]
from results r2
where r2.id = r.id
and r2.result =
(
select min(r3.result)
from results r3
where r3.id = r.id
)
) as minResultDate,
(
select r2.result
from results r2
where r2.id = r.id
and r2.[date] =
(
select min(r3.[date])
from results r3
where r3.id = r.id
)
) as firstResult,
max(r.result) as maxResult,
(
select r2.[date]
from results r2
where r2.id = r.id
and r2.result =
(
select max(r3.result)
from results r3
where r3.id = r.id
)
) as maxResultDate
from results as r
group by r.id
order by r.id
