By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,746 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,746 IT Pros & Developers. It's quick & easy.

can this be done with one query?

P: n/a
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
thanks for the info

jerry

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"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
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.