CODEWITHSUNDEEP
sqlsql-server
satoshi
satoshi

Reputation: 439

Get the MAX of combined CASE statements

I have 4 CASE statements and I want to know how can I only display the MAX of the calculation.

I have tried combining my CASE statements with the COALESCE function.

SELECT
ID,
CASE 
    WHEN tw.displayText IN ('A','B','C') THEN 'A1'
    WHEN tw.displayText IN ('D','E','F') THEN 'A2'
    WHEN tw.displayText IN ('G','H','I') THEN 'A3'
    ELSE NULL
    END AS COL1

How can I make COL1 be A3 if the ID can take all 3 (A1,A2,A3)

ID COL1
1  A1
1  A2
1  A3

ID COL1
1  A3

Upvotes: 0

Views: 361

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

SQL Server doesn't have a greatest() or least() function. But you can do this in the from clause using apply:

select COL1
from tw cross apply
     (select max(val) as col1
      from (values (CASE WHEN tw.displayText IN ('A','B','C') THEN 1 END),
                   (CASE WHEN tw.displayText IN ('D','E','F') THEN 2 END),
                   (CASE WHEN tw.displayText IN ('G','H','I') THEN 3 END)
           ) v(val)
     ) v;

There may be other ways to phrase this. This is a pretty general solution.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Although the wording of your question is unclear, if my mind-reading skills are on point today, I think this is all you need:

SELECT CASE 
  WHEN tw.displayText IN ('G','H','I') THEN 3 
  WHEN tw.displayText IN ('D','E','F') THEN 2
  WHEN tw.displayText IN ('A','B','C') THEN 1 
  ELSE NULL
END AS COL1

Why? Because a single CASE expression is evaluated such that the first WHEN (from top to bottom) to evaluate as true is the one that will be returned by the expression. The rest will be ignored.

So in this expression, if the conditions that would result in a "3" are true, then "3" is returned and the other conditions are ignored. If "3" isn't true, then the conditions for "2" are checked. And so on.

Now if you plan on using GROUP BY and actually getting an Aggregate, then you would need to wrap this entire CASE..END expression in a MAX() function.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I think you want only one case expression with max() :

SELECT MAX(CASE WHEN tw.displayText IN ('A','B','C') THEN 1
                THEN 1
                WHEN tw.displayText IN ('D','E','F') THEN 2
                THEN 2
                WHEN tw.displayText IN ('G','H','I') THEN 3
                THEN 3
           END) AS COL1

MAX() is really unnecessary because case expression only return single value & will evaluate only once whenever any of them conditions are mat.

Upvotes: 0

Related Questions