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

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 04/07/04

  • Next message: Gabe Knuth: "Re: Getting values from other fields using min & max aggregates" 
    Date: Wed, 07 Apr 2004 22:47:06 +0200

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

    >Ok, that works to the point where I get the error message that you mentioned.
    >
    >The low score appears 3 times in the table, which I assume is causing this problem. Will having two recordsets solve the problem? One for min and one for max?

    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)
  • Next message: Gabe Knuth: "Re: Getting values from other fields using min & max aggregates"