Get other fields while using min & max aggregate functions

From: Gabe Knuth (anonymous_at_discussions.microsoft.com)
Date: 04/07/04 

Date: Wed, 7 Apr 2004 12:01:04 -0700

Hi all. I tried to search the forum 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_PersonID
1 ........41...........5.......................... 69............3

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

Relevant Pages