Duplicate records when distinct on case-determined field-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

Your query needs to aggregate [col] in your preferred way, by selecting [f1], [f2] and an aggregation over [col], for example:

SELECT [f1],[f2],MAX([col]) as [flag] 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

This would select the maximum, but you can choose other aggregations if you prefer.