MAX Evaluation on a CASE statement

Question

Consider these two queries:

--Query 1
SELECT ID, CASE 
    		WHEN MAX(Col1) > 0 THEN		'String1' 
    		WHEN MAX(Col2) > 0 THEN		'String2'
    		ELSE				'String3' 
    	END AS Status
FROM SomeTable
GROUP BY ID

--Query 2
SELECT ID, MAX(CASE 
    		WHEN Col1 > 0 THEN		'String1' 
    		WHEN Col2 > 0 THEN		'String2'
    		ELSE			        'String3' 
    	END) AS Status
FROM SomeTable
GROUP BY ID

Using some real data I am getting the same result with both queries. Should this be the case or is this only by coincidence?

Is the MAX in Query 2 evaluating the VARCHAR's ('String1', 'String2', 'String3') or the expressions for each case?

Answer 1

0

I suspect it is coincidence - what is your result everytime? The 2 queries are doing very different things.

The MAX in query2 is evaluating the VARCHARs.

For example, with this data

create table sometable (id int, col1 int, col2 int)
insert into sometable
select 1,0,0
union select 1,1,2
union select 1,0,2
union select 2,0,0
union select 2,0,0

Query 1 gives

ID          Status
----------- -------
1           String1
2           String3

Query 2 gives

ID          Status
----------- -------
1           String3
2           String3

answered Oct 30 '09 at 05:18 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

(comments are locked)

Answer 2

I's absolutely coincidence, as the following data and detail query illustrate.

All functions evaluate the expressions contained within their brackets. The order of who is evaluating whom DOES make a difference.

Query 2 gets the MAX() of the values returned by the CASE expression. The CASE expression can return a value of "string1", "string2", or "string3". Therefore if any row evaluates to a "string3" it's value would be returned by the MAX() function. In the absence of a "string3" any "string2" will be returned. (Of couse, "string1" is last.) This has the effect of looking at all rows for all columns before deciding on a result.

Query 1 on the other hand uses its CASE expression to evaluate the MAX() of the values in both col1 and col2. In this instance, if any row has a col1 value > 0 then MAX(col1) will be greater than 0 and the case expression will return "string1", never moving on to check MAX(col2). This has the effect of evaluating the columns 1 at a time, in the order specified by the CASE expression.

declare @someTable table (rowid int identity(1,1),ID int, col1 int, col2 int)
insert into @sometable
select 1, 2, 3 union all
select 1, 0, 4

SELECT -- Detail, the devil is always in the details
        rowID,ID, col1, col2, CASE 
                WHEN Col1 > 0 THEN              'String1' 
                WHEN Col2 > 0 THEN              'String2'
                ELSE                            'String3' 
        END AS Status
FROM @sometable        

SELECT -- query 1
        ID, CASE 
                WHEN MAX(Col1) > 0 THEN         'String1' 
                WHEN MAX(Col2) > 0 THEN         'String2'
                ELSE                            'String3' 
        END AS Status
FROM @SomeTable
GROUP BY ID



SELECT --Query 2
       ID, MAX(CASE 
                WHEN Col1 > 0 THEN              'String1' 
                WHEN Col2 > 0 THEN              'String2'
                ELSE                            'String3' 
        END) AS Status
FROM @SomeTable
GROUP BY ID

MAX Evaluation on a CASE statement

Follow this question

Related Questions