Which One Is the Culprit?
Published: August 8, 2007
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.
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
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:
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.
Post this story to del.icio.us
Post this story to Digg
Post this story to Slashdot