CONDITIONAL SELECT - HELP

Good stuff here - I like the other options!

If you don't need the identity column, or any other non-grouped data, I think you can do it all in one step, like so:

SELECT

Code

,COALESCE(MAX(CASE WHEN Type = 'CORRECTED' THEN Amount ELSE NULL END),

MAX(CASE WHEN Type = 'ACTUAL' THEN Amount ELSE NULL END),

MAX(CASE WHEN Type = 'ESTIMATED' THEN Amount ELSE NULL END)) AS Amount

,COALESCE(MAX(CASE WHEN Type = 'CORRECTED' THEN 'CORRECTED' ELSE NULL END),

MAX(CASE WHEN Type = 'ACTUAL' THEN 'ACTUAL' ELSE NULL END),

MAX(CASE WHEN Type = 'ESTIMATED' THEN 'ESTIMATED' ELSE NULL END)) AS Type

,[Month]

,[Year]

FROM #Test

GROUP BY Code,[Year],[Month]

ORDER BY Code,[Year],[Month]

It is simple and good logic

[n/a, removed]

Only on 2005 and above, but it is an example of how sophisticated the query optimiser is.

In this case, it recognises that your SQL2K-compatible syntax is logically equivalent to a MIN(CASE...END) OVER (...) construction. Doing that transformation (exploration) results in a much better plan, with just the single scan and a common sub-expression spool.

Not too many SQL DBAs would be able to make that transformation by visual inspection. The optimiser is a truly amazing piece of kit.

Paul

Can't disagree.

It just a bit unpredictable sometimes...:-D

My favoured one is "devision by zero error" in

SELECT Col1/Col2 FROM MyTable WHERE Col2 != 0

(I know I 've already mentioned it few times here...):hehe:

We can help the optimiser even more by adding a computed column. This won't add any data to the table - it's just a metadata change - but the effect on the query plan is dramatic, producing one of my all-time favourites: the Segment Top...

-- Not persisted!

ALTER TABLE #Test

ADD sort_value

AS CASE [Type]

WHEN 'ESTIMATED' THEN 200

WHEN 'ACTUAL' THEN 400

WHEN 'CORRECTED' THEN 600

END;

My submission is then:

SELECT T.Code,

T.Amount,

T.Type,

T.[Month],

T.[year]

FROM #Test T

WHERE T.sort_value =

(

SELECT MAX(T2.sort_value)

FROM #Test T2

WHERE T2.Code = T.Code

AND T2.[year] = T.[year]

AND T2.[Month] = T.[Month]

);

Query plan (with Segment Top):

We don't even have to explicitly reference the computed column to get this nice plan:

SELECT T.Code,

T.Amount,

T.Type,

T.[Month],

T.[year]

FROM #Test T

WHERE CASE T.[Type] WHEN 'ESTIMATED' THEN 200 WHEN 'ACTUAL' THEN 400 WHEN 'CORRECTED' THEN 600 END =

(

SELECT MAX(CASE T2.[Type] WHEN 'ESTIMATED' THEN 200 WHEN 'ACTUAL' THEN 400 WHEN 'CORRECTED' THEN 600 END)

FROM #Test T2

WHERE T2.Code = T.Code

AND T2.[year] = T.[year]

AND T2.[Month] = T.[Month]

);

Clever stuff. Another benefit is that SQL Server (or you) can create statistics on the computed column to give the optimiser information about the distribution and frequency of values resulting from that CASE expression - a very powerful technique.

Paul

That's not unpredictable...it's a (good) design trade-off.

It's the price you pay for the amazing tricks the QO can do for you: You cannot safely write code that depends on order of evaluation of scalar expressions (or even the number of times the expression might be evaluated) - though there are some supported exceptions to that, CASE expression evaluation order for example.

My personal view is that this is a price well worth paying. I don't regard "SELECT Col1/Col2 FROM MyTable WHERE Col2 != 0" as a particularly shining example of good coding anyway 😛

Paul

I guess the only problem with such "trade-offs" that they come as new hidden features, but they are not expected by most of the existing users. Who will really love it, especially after they would successfully test upgrading from SQL2000 to SQL2005, and start to have some strange behaviour in production (which not always reproducable). I don't think it was mentioned by MS in their SQL2005 release "New Grate SQLServer Features" or "Warning! Be Aware" papers.

I agree, that no one should write code that depends on order of evaluation of scalar expressions. The problem is: there are a lot of code already written. You know, that it is now and then, someone here having problem which is direct result of it.

Actually, I am wrong here, it is not a problem - it is a bonanza for contractors :-D. I always say that if it not MS, we would have much less jobs around...

"SELECT Col1/Col2 FROM MyTable WHERE Col2 != 0" is a bit grotesque. But I think it's quite representative for this feature.

SQL Developers needs to be aware that this can happen with any kind of expression including "safe" datatype convertions (it was one thread about it today).