CODEWITHSUNDEEP
sqlsql-server
Aurelio Brunod
Aurelio Brunod

Reputation: 13

Duplicate records when distinct on case-determined field

My SP returns a BIT column depending on wheter a parameter equals to another table field.

SELECT DISTINCT
table.[f1],
table.[f2],
--Many more fields
CASE WHEN table.MyField = @param THEN 1 ELSE 0 END AS col
--Where condition not relevant

Said column is in a large DISTINCT statement. Whenever the column is true, it gets "distincted" and duplicates the record, while I need only one: true if match, otherwise false.

This is how it's returning:

f1   f2   flag
FOO  BAR  0
FOO  CAR  0
GOO  BAR  1
GOO  BAR  0

I need this:

f1   f2   flag
FOO  BAR  0
FOO  CAR  0
GOO  BAR  1

I tried using a subquery inside the CASE block, and pulling the generated column outside of the distinct. First option gave me wrong result set, and the second doesn't aggregate. Last try I wrapped everything in another SELECT and used a GROUP BY:

SELECT * FROM (
SELECT DISTINCT
table.[f1],
table.[f2],
CASE WHEN table.MyField = @param THEN 1 ELSE 0 END AS col
) AS t GROUP BY f1, f2

After playing with it for a while, It either gave me the same result or an error on the fields to aggregate. Adding a where condition gives me all kinds of wrong result sets.

Upvotes: 0

Views: 207

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131453

What you posted shows you want the maximum flag value for those field combinations, not just distinct rows. You can use GROUP BY for this:

SELECT f1,f2, MAX(IIF(MyField=@param,1,0)) as Flag
FROM Table1
GROUP BY f1,f2

DISTINCT and GROUP BY work in similar ways, but DISTINCT applies to all column values, even those calculated by an expression.

Upvotes: 2

Related Questions