CODEWITHSUNDEEP
sqlselectduplicates
dan6657
dan6657

Reputation: 117

SQL Removing Duplicate rows

I've been trying to remove duplicates using HAVING count(*) > 1, group by, distinct and sub queries but can't get any of these to work..

SELECT UserID,  BuildingNo    
FROM  Staff INNER JOIN TblBuildings ON Staff.StaffID = 
TblBuildingsStaffID
GROUP BY TblStaff.User_Code, BuildingNo

What I get is..

StaffID1 | BuildingNo1
StaffID1 | BuildingNo2
StaffID2 | BuildingNo2
StaffID3 | BuildingNo1
StaffID3 | BuildingNo2

I'm trying to get it so it just displays staff with one building number (if they have two regardless of which it shows) like:

StaffID1 | BuildingNo1
StaffID2 | BuildingNo2
StaffID3 | BuildingNo1

It can't be too hard.. I've tried CTE's left joining the building to the staff table, these come up NULL for some reason when I try this

Any help would be great!

Upvotes: 0

Views: 73

Answers (3)

NiKhil Kutewalla
NiKhil Kutewalla

Reputation: 103

try this - 

SELECT distinct UserID,  BuildingNo    
FROM  Staff INNER JOIN TblBuildings ON Staff.StaffID = 
TblBuildingsStaffID

Upvotes: 0

user330315
user330315

Reputation:

Don't group by BuildingNo, then you can use having to filter out the groups you want.

SELECT s.UserID, min(b.BuildingNo) as buildingno
FROM Staff s
   JOIN TblBuildings ON s.StaffID = b.TblBuildingsStaffID
GROUP BY s.UserID
having count(distinct b.BuildingNo) = 1;

The min() aggregate is required because buildingno is not part of the group by clause. But as the having() clause only returns those with one building, it doesn't change anything.

If you want to display all staff members, and simply pick one (arbitrary) building, then simply leave out the having condition.

If you want to include staff members without a building you need a left join: 

SELECT s.UserID, min(b.BuildingNo) as buildingno
FROM Staff s
   LEFT JOIN TblBuildings b ON s.StaffID = t.TblBuildingsStaffID
GROUP BY t.UserID;

Upvotes: 2

Mayank
Mayank

Reputation: 407

Use row partition keyword in your query to avoid duplicacy

   WITH CTE AS( SELECT   ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY UserID )  AS 'Num',UserID,  BuildingNo    
    FROM  Staff INNER JOIN TblBuildings ON Staff.StaffID = 
    TblBuildingsStaffID
    GROUP BY TblStaff.User_Code, BuildingNo)
SELECT * FROM CTE
WHERE Num =1

Upvotes: 0

Related Questions