How would I select the column that contains the highest value for each row?
ID Store Shoes Shirts Pants 01 A 1 2 3 02 B 3 1 2 03 C 2 3 1
I want to return something like:
A Pants B Shoes C Shirts
To get exactly what you asked for:
select Store , case when Shoes >= Shirts and Shoes >= Pants then 'Shoes' when Shirts >= Shoes and Shirts >= Pants then 'Shirts' when Pants >= Shoes and Pants >= Shirts then 'Pants' else null end from Stores
Ugly, and even uglier if you ever have to handle additional columns. Feel free to imagine what the query would look like if there were a realistic number of columns, like 187.
What you should do is have two tables instead of one:
Stores ID Store 01 A 02 B 03 C Inventory ID Item Qty 01 'Shoes' 1 01 'Shirts' 2 01 'Pants' 3 02 'Shoes' 3 02 'Shirts' 1 02 'Pants' 2 03 'Shoes' 2 03 'Shirts' 3 03 'Pants' 1
Now you can handle as many items as you wish with this query:
select Store , Item from Stores S inner join Inventory I on S.ID = I.ID where Qty = ( select max(Qty) from Inventory where ID = I.ID )
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 April 2004