Re: Getting values from other fields using min & max aggregates

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 04/07/04 

Date: Thu, 8 Apr 2004 01:09:16 +0530

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@yahoo.co.in