Highest value in each row

In the database example below, I would like the HiGame column to display the highest game from each bowler's game columns.

Name  Game1  Game2   Game3   HiGame 
Rick  200    210     195     (210)
Frank 195    186     145     (195)

I've tried to find a way to use the MAX function, but nothing works. Is what I want possible?


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Yes, what you want is possible, but it's ugly. And it doesn't scale.

MAX is not right for your table. MAX is an aggregate, used to find the highest value in a column of data. You have to find the highest row value yourself.

select Name
     , Game1
     , Game2
     , Game3
     , case when Game1 >= Game2
             and Game1 >= Game3
            then Game1
            when Game2 >= Game1
             and Game2 >= Game3
            else Game3
        end as HiGame
  from yourtable

See what happens when you add a 4th game:

select Name
     , Game1
     , Game2
     , Game3
     , Game4
     , case when Game1 >= Game2
             and Game1 >= Game3
             and Game1 >= Game4
            then Game1
            when Game2 >= Game1
             and Game2 >= Game3
             and Game2 >= Game4
            when Game3 >= Game1
             and Game3 >= Game2
             and Game3 >= Game4
            else Game4
        end as HiGame
  from yourtable

Clearly, a scaling nightmare. However, if you were to store your data like this:

Name  Game
Rick   200    
Rick   210
Rick   195
Frank  195
Frank  186
Frank  145

Then you could run this query no matter how many games each player played.

select Name
     , max(Game) as HiGame
  from yourtable
group
    by Name

Sweet, eh?

For More Information


This was first published in May 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top