| re: can this be done with one query?
"sparks" <sparks@here.com> wrote in message
news:iqd6d0dpqst0okn14ovdgt07f7cc0tvugh@4ax.com...[color=blue]
> 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
>
>[/color]
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
| 
