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
Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation