CODEWITHSUNDEEP
sqlsql-server
Gargoyle
Gargoyle

Reputation: 10345

SQL filter out rows with duplicate cell value

I've got a big query that's returning rows where one of the colums has duplicated values, for example:

| sysid | col1 | col2
| A     |  1   |  2
| A     |  2   |  3
| B     |  1   |  4

sysid is the column I want to filter by, so in the above example I only want the last row of output because A appears more than once. I tried to do this to filter them out:

CREATE TABLE #temp (SysId VARCHAR(10), col1 INT);
INSERT INTO #temp VALUES ('A', 1), ('B', 1), ('A', 1);

WITH cte AS (
   SELECT * FROM #temp
), sysids AS (
   SELECT SysId FROM #temp GROUP BY Sysid HAVING COUNT(*) = 1
)
SELECT * FROM #temp WHERE EXISTS (SELECT SysId FROM sysids);

DROP TABLE #temp;

I expected that final select would only contain the B row, in the above example . I'm still getting all of them though, and I don't understand why.

Obviously in this specific example I don't need the cte part but my real query is pretty complex with multiple unions.

Upvotes: 2

Views: 117

Answers (3)

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

Please try this:

;WITH cte AS (
    SELECT t.SysId,t.col1,COUNT(1)OVER(PARTITION BY t.SysID) AS [cnt] 
    FROM #temp t
) 
SELECT c.SysId,c.col1
FROM cte c WHERE c.cnt = 1;

Upvotes: 1

Rasanjana N
Rasanjana N

Reputation: 1400

CREATE TABLE #temp (SysId VARCHAR(10), col1 INT);
INSERT INTO #temp VALUES ('A', 1), ('B', 1), ('A', 1);

WITH cte AS (
   SELECT * FROM #temp
), sysids AS (
   SELECT SysId FROM #temp GROUP BY Sysid HAVING COUNT(SysId) =1
   )
SELECT * FROM #temp WHERE SysId IN (SELECT SysId FROM sysids);

DROP TABLE #temp;

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

You can use a correlated, aggregating subyquery and NOT EXISTS.

SELECT t1.*
       FROM #temp t1
       WHERE NOT EXISTS (SELECT ''
                                FROM #temp t2
                                WHERE t2.sysid = t1.sysid
                                HAVING count(*) > 1);

Upvotes: 2

Related Questions