CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

Find out the Duplicate Records in Sql-server

In our application, we have some tables storing staff data. I need to find out whether the same office is allocated to a staff member multiple times. How can I figure this out in SQL Server?

I have tried below query but it didn't give me the correct solution.

SELECT DISTINCT STAFFID, officeid
FROM [stafftable]
WHERE rowstatus = 'A'
GROUP BY STAFFID, officeid
HAVING COUNT(staffid) > 1
    AND COUNT(officeid) > 1
ORDER BY STAFFID, officeid

It returns the staff members allocated to different offices also. Any help would be greatly appreciated.

Upvotes: 1

Views: 60

Answers (3)

D Stanley
D Stanley

Reputation: 152491

You can use COUNT(*) to count all rows in each group:

SELECT STAFFID, officeid
FROM [stafftable]
WHERE rowstatus = 'A'
GROUP BY STAFFID, officeid
HAVING COUNT(*) > 1
ORDER BY STAFFID, officeid

Upvotes: 2

tshoemake
tshoemake

Reputation: 1351

;With cte 
as (
select * row_number() over(partition by STAFFID, officeid )as count from stafftable
where rowstatus = 'A'
)
select * from cte where count > 1

Upvotes: 1

Devart
Devart

Reputation: 121902

SELECT *
FROM (
    SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY STAFFID, officeid ORDER BY STAFFID)
    FROM dbo.stafftable
    WHERE rowstatus = 'A'
) t
WHERE t.RowNum > 1

Upvotes: 1

Related Questions