Which One Is the Culprit?
August 8, 2007 Hey, Ted
I am using the MAX function in a SQL statement to get the maximum value from a few columns. I don’t know which element is producing the maximum value. Is there a way to identify the element that has the maximum value?
Yes, Hisham. Thanks for allowing me another opportunity to show off that most versatile of SQL features: CASE.
For the benefit of the readers, here’s the SQL statement Hisham sent to me.
SELECT max(WK01, WK02, WK03, WK04) FROM SALES
Here’s the sort of results his SQL query generates.
MAX === 100 120 150
He has the maximum value from each input row (record), but how does he know which column (field) had the maximum value? Like this:
SELECT MAX(wk01, wk02, wk03, wk04), CASE WHEN wk01 = MAX(wk01, wk02, wk03, wk04) THEN 1 WHEN wk02 = MAX(wk01, wk02, wk03, wk04) THEN 2 WHEN wk03 = MAX(wk01, wk02, wk03, wk04) THEN 3 WHEN wk04 = MAX(wk01, wk02, wk03, wk04) THEN 4 END AS MaxPos FROM SomeTable
In case of a tie, the CASE expression returns the position of the first one.
Suppose the input data looks like this:
Wk01 Wk02 Wk03 Wk04 ==== ==== ==== ==== 25 100 75 80 120 100 55 110 120 150 150 90
Now the results look like this:
MAX MaxPos === ====== 100 2 120 1 150 2
CASE is a marvelous tool. I find new uses for it often, and we’ve presented several examples of the use of CASE in previous editions of Four Hundred Guru.