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?
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
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in May 2004