Results 1 to 6 of 6

Thread: Max function

  1. #1
    Join Date
    Apr 2002
    Posts
    168

    Unanswered: Max function

    I have a table like this :


    Name Score1 Score2 Score3 Score4
    John 100 50 200 150
    Smith 15 12 250 100


    What I need to find is the max score for each person, so the result should be like this :


    Name Max
    John 200
    Smith 250

    Does anyone know how to do this ?



    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb More info

    Score 1: Score 2: Score 3

    Are these seperate fields?

    Or can you post the query that generates the output you have at present

  3. #3
    Join Date
    Apr 2002
    Posts
    168
    Yes they are.

    In excel, it's very easy to do this since they are max function to do this.

  4. #4
    Join Date
    Jan 2003
    Posts
    126
    Try something like this:

    Code:
    select [name], (case when (case when score3 < score4 then score4 else score3 end)
    		<(case when score1 < score2 then score1 else score2 end) then
    		 (case when score3 < score4 then score4 else score3 end) else
    		 (case when score3 < score4 then score4 else score3 end)end) from table
    HTH

    Ken

  5. #5
    Join Date
    Apr 2002
    Posts
    168
    Thanks

    What I did was using :


    select name, score1 from table
    union select name, score2 from table
    etc

    Then group by the above query by name and find the max. And then, merge it back to the original table.

    What I was not sure is whether to use union select OR union all select in this case ?

  6. #6
    Join Date
    Jan 2003
    Posts
    126
    Hi ya,

    I tried this:

    Code:
    select [name], max(Score) from
    (select [name],s1 as Score from a union  select [name],s2 from a union select [name],s3 from a union select [name],s4 from a) as d
    group by [name]
    s1 is score1 and so on, it works as well.

    You'll want to use union all if there are duplicate values for a person's score. However if your just finding the max it's a moot point because if the max value has a duplicate it doesn't matter.

    Hope that helps,

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •