Search code examples
sqlsql-server

SQL WHERE NOT EXISTS (skip duplicates)

I'm struggling to get the query below right. What I want is to return rows with unique names and surnames. What I get is all rows with duplicates

This is my SQL

DECLARE @tmp AS TABLE (Name VARCHAR(100), Surname VARCHAR(100))

INSERT INTO @tmp

SELECT CustomerName,CustomerSurname FROM Customers
WHERE 
NOT EXISTS 
(SELECT Name,Surname 
 FROM @tmp 
 WHERE Name=CustomerName 
 AND ID Surname=CustomerSurname
GROUP BY Name,Surname )

Please can someone point me in the right direction here. //Desperate (I tried without GROUP BY as well but get same result)

Solution

  • DISTINCT would do the trick.

    SELECT DISTINCT CustomerName, CustomerSurname
FROM Customers

    Demo

    If you only want the records that really don't have duplicates (as opposed to getting duplicates represented as a single record) you could use GROUP BY and HAVING:

    SELECT CustomerName, CustomerSurname
FROM Customers
GROUP BY CustomerName, CustomerSurname
HAVING COUNT(*) = 1

    Demo