Which One Is the Culprit?

Published: 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?

--Hisham

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.

--Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot