CODEWITHSUNDEEP
sql-server-2008
Daniel Erb
Daniel Erb

Reputation: 61

SQL Query Removing Duplicates

So A quick question about an SQL query,

I have a table containing VendorNames, VendorCity and VendorStates. I would like to pull these 3 columns but only return one result per city and state. In other words I would like the query to return results where a vendor does not have a city and state in common with another vendor.

The table name is Vendors and the column names above are correct.

Upvotes: 0

Views: 62

Answers (2)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SELECT VendorCity, VendorStates, min(VendorNames)
FROM Vendors
GROUP BY VendorCity, VendorStates
HAVING count(VendorNames) = 1

Upvotes: 2

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT V.* 
FROM VendorTable V
     JOIN 
     (
         SELECT VendorCity, 
             VendorStates,
             Count(*) AS NoOfVendorNames
         FROM VendorTable
         GROUP BY VendorCity,
                  VendorStates
     ) T
     ON V.VendorCity = T.VendorCity
         AND V.VendorStates = T.VendorStates
WHERE  T.NoOfVendorNames = 1

Upvotes: 0

Related Questions