CODEWITHSUNDEEP
sqlsql-server
Reisclef
Reisclef

Reputation: 2148

Get column value where boolean is true, otherwise get the column value where boolean false

I have a table with data similar to the following.

I've been using a large table with numerous rows with varying flags, and keys. I've managed to group them down so that I have the lowest where the flag is true, and the lowest where the flag is false.

╔══════════════════╦══════╦═══════╗
║      Email       ║ Flag ║  Key  ║
╠══════════════════╬══════╬═══════╣
║ [email protected]   ║    1 ║ 77731 ║
║ [email protected]   ║    0 ║ 67980 ║
║ [email protected]   ║    1 ║ 64417 ║
║ [email protected]   ║    0 ║ 71733 ║
║ [email protected] ║    1 ║ 95655 ║
║ [email protected]  ║    0 ║ 91016 ║
╚══════════════════╩══════╩═══════╝

Now, for each distinct email, if there is a true AND false flag, I want to return the true Key value. Otherwise, I want to return the lowest value.

So the output would ideally look like this:

╔══════════════════╦══════╦═══════╗
║      Email       ║ Flag ║  Key  ║
╠══════════════════╬══════╬═══════╣
║ [email protected]   ║    1 ║ 77731 ║
║ [email protected]   ║    1 ║ 64417 ║
║ [email protected] ║    1 ║ 95655 ║
║ [email protected]  ║    0 ║ 91016 ║
╚══════════════════╩══════╩═══════╝

I've been trying all kinds of grouping, having clauses, using case statements in the previous two, but can't see how to do so.

I only really need the email and the Key, if that helps.

Upvotes: 1

Views: 728

Answers (3)

David Faber
David Faber

Reputation: 12485

You could also accomplish this through a window function:

SELECT email, flag, key FROM (
    SELECT email, flag, key, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY flag DESC ) AS rn
      FROM mytable
) x1
 WHERE rn = 1;

Upvotes: 0

MattMcGowan
MattMcGowan

Reputation: 117

SELECT
    A.Email
    ,CASE WHEN A.Cf > A.Sf THEN Mk ELSE Key END AS ChosenKey
FROM
    (
    SELECT
        Email
        ,Key
        ,COUNT(Flag) Cf
        ,MIN(Key) OVER(PARTITION BY Email ORDER BY Key ASC) AS Mk
        ,SUM(Flag) OVER(PARTITION BY Email) Sf
    FROM
        Table
    ) AS A

Maybe work from something like that? And then remove your duplicates etc.

Upvotes: 0

DavidG
DavidG

Reputation: 118947

There's lots of ways, here are two.

Method 1

You could do it with a CTE like this:

WITH data_cte AS (
    SELECT Email, MAX(CAST(Flag AS INT)) AS Flag
    FROM Data
    GROUP BY Email)
SELECT Data.* 
FROM data_cte
JOIN Data 
    ON Data.Email = data_cte.Email
    AND Data.Flag = data_cte.Flag

To de-construct it, the CTE part just gets the MAX value of flag for each email (need to CAST to INT as you can't MAX on a BIT column) and the rest of the query joins the CTE back to the table to get the relevant data rows.

Method 2

Using a UNION:

SELECT *
FROM Data
WHERE Flag = 1

UNION 

SELECT *
FROM Data
WHERE Flag = 0
AND NOT EXISTS(SELECT * 
               FROM Data AS InnerData
               WHERE InnerData.Flag = 1 
               AND InnerData.Email = Data.Email)

Upvotes: 1

Related Questions