CODEWITHSUNDEEP
sqlsql-serversql-server-2012
felloffthestack
felloffthestack

Reputation: 35

How to GROUP BY with a CASE statement?

My SQL example is as follows...

DECLARE @a TABLE(
    id INT,
    val BIT
);
INSERT INTO @a(id,val) VALUES (1,0),(2,0),(3,1),(4,0),(5,1);

SELECT
    id = CASE
        WHEN val = 1 THEN id
        ELSE MAX(id)
    END
FROM @a
WHERE val = 0
GROUP BY id, val;

What my result is:

id
---
 1
 2
 4

What I am trying to get...

id
---
 4

Or if I did "WHERE val = 1" then what I want is...

id
---
 3
 5

What I'm basically trying to say is "if I'm selecting where val = 1, I want all the rows, but if I'm selecting where val = 0 I only want the row with the highest id". Any ideas?

Upvotes: 0

Views: 90

Answers (2)

Philippe Banwarth
Philippe Banwarth

Reputation: 17755

I am not sure that I understand what you are trying to do, but the following query gives the expected result with WHERE val = 1 or WHERE val = 0. The idea is to move the case to the GROUP BY clause

SELECT MAX(id)
FROM @a
WHERE val = 1
GROUP BY CASE WHEN val = 1 THEN id ELSE 0 END

Upvotes: 2

Hogan
Hogan

Reputation: 70528

You can use the UNION statement to combine tables. So if we split what you want to do into two select statements we get the following if the WHERE = 0

SELECT MAX(id) 
FROM @a
GROUP BY ID, VAL

UNION

SELECT ID
FROM @a
WHERE val = 0 AND val = 1
GROUP BY ID, VAL

And the following if the WHERE = 1

SELECT MAX(id) 
FROM @a
GROUP BY ID, VAL

UNION

SELECT ID
FROM @a
WHERE val = 1 AND val = 1
GROUP BY ID, VAL

This might make clear why changing the where clause to have it do something different seems strange to me. Clearly in the first one the part after the union is silly/useless. In the 2nd it also seems strange to have the same expression twice in the where clause. But this meets your requirements to change one value in a where and have two different results.

Maybe you can explain your requirements better now that you see the solution?

Upvotes: 0

Related Questions