Hi all. I tried to search the newsgroup for this, but I'm not entirely sure how to phrase it to get the best results, so I figured I'd post and hope someone can help me out.

I've got a SQL statment that retrieves the minimum and maximum values from the Score field in a table called Scores (original, I know). What I'd like to do is retreive another field called PersonID for each of the two results I get. Basically, I'd like to have the output look like this

Record Score_Min Score_Min_PersonID Score_Max Score_Max_PersonI
1 ........41...........5.......................... 69............

The current SQL statement I have is below, and works great for returning only the min and max scores

SELECT Score_Min=MIN(dbo.Scores.Score), Score_Max=MAX(dbo.Scores.Score) FROM dbo.Scores WHERE Score <> 0

Thanks in advance for your help

Gabe

Try:

SELECT Score_Min=MIN(b.Score),
Score_Min_PersonID=(select personid from Scores a
where a.score = min(b.score)),
Score_Max=MAX(b.Score),
Score_Max_PersonID=(select personid from Scores a
where a.score = max(b.score))
FROM dbo.Scores b
WHERE Score <> 0


Make sure that you have only one row for each of the max(score) and min(score) otherwise you
will get an error as

"Subquery returned more than 1 value......."

--
Vishal Parkar
vgparkar (AT) yahoo (DOT) co.in

On Wed, 7 Apr 2004 13:26:13 -0700, Gabe Knuth wrote:

In that case, try:

SELECT Score_Min=MIN(b.Score),
Score_Min_PersonID=(select MAX(personid) from Scores a
where a.score = min(b.score)),
Score_Max=MAX(b.Score),
Score_Max_PersonID=(select MAX(personid) from Scores a
where a.score = max(b.score))
FROM dbo.Scores b
WHERE Score <> 0

This will give you one of the persons scoring the highest score and
one of the persones scoring the lowest score. In both cases: the one
with the highest personid.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)