Reputation: 35
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
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
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