sqlsalesforcecrmexacttarget

SQL Duplicate rows

I am writing a SQL query for my CRM.

My table looks like below:

EML SubKey
----------
 A    A
 A    1
 B    B
 C    2

I want to select unique emails and duplicate emails with non-numeric subkeys.

Please help me :)

The result should look like

A A
B B
C 2


SELECT DISTINCT
EML
FROM _ListSubscribers

SELECT SubscriberKey 
FROM _ListSubscribers
WHERE ISNUMERIC(SubscriberKey) = 0
Solution

  • Updated query should give you required result :

    SELECT 
  EML
  , SubscriberKey 
FROM _ListSubscribers 
WHERE EML IN (
    SELECT EML 
    FROM _ListSubscribers 
    GROUP BY EML 
    HAVING COUNT(*) = 1
) 

UNION 

SELECT 
  EML
  , SubscriberKey 
FROM _ListSubscribers 
WHERE EML IN (
    SELECT EML 
    FROM _ListSubscribers 
    GROUP BY EML 
    HAVING COUNT(* ) > 1
) AND ISNUMERIC(SubscriberKey) = 0