Consider these two queries:
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?
asked Oct 30, 2009 at 02:17 AM in Default
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
Query 1 gives
Query 2 gives
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
answered Oct 30, 2009 at 10:10 AM