x

MAX Evaluation on a CASE statement

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?

more ▼

asked Oct 30, 2009 at 02:17 AM in Default

Simon Hartcher gravatar image

Simon Hartcher
23 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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 
more ▼

answered Oct 30, 2009 at 05:18 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 30, 2009 at 10:10 AM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x990

asked: Oct 30, 2009 at 02:17 AM

Seen: 12922 times

Last Updated: Oct 30, 2009 at 02:17 AM