Checking for duplicates in a database table

[staja84f]

I'm trying to locate duplicate data in a table using 4 columns:
employee_id (primary key), employeeid, lastname and firstname.

I can pull up the duplicate data with the last three listed columns,
but when I include the first column (employee_id) in the statement, it
shows nothing, since the employee_id value is unique for each row.
Here's the statement I'm currently using:

select employeeid, firstname, lastname
from empmain
group by employeeid, firstname, lastname
having count(*) 1

The only thing I need extra from this statement is a separate row for
each duplicate value, showing the unique employee_id value. I know I
need to take the Group By option out, but I get the error "Column
'empmain.employeeid' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.".
Tried doing research on this issue, but honestly, my knowledge of SQL
is limited and I'm really not sure I'm asking the right questions when
searching. Any help would be appreciated.

- Travis

[Plamen Ratchev]

On SQL Server 2005 you can do this:

WITH Dups
AS
(SELECT employee_id, employeeid, firstname, lastname,
COUNT(*) OVER(PARTITION BY employeeid, firstname, lastname) AS
cnt
FROM empmain)
SELECT employee_id, employeeid, firstname, lastname
FROM Dups
WHERE cnt 1;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Philipp Post]

Travis,

this is not too beautifull but at least it gives the desired result:

SELECT FirstName, LastName, EmployeeID
FROM Employees AS E1
WHERE FirstName IN (SELECT FirstName
FROM Employees AS E2
GROUP BY FirstName, LastName
HAVING Count(*) 1
AND LastName = E1.LastName)

The subquery filters out the duplicate names and connects it in the
outer query to the EmployeeID.

Brgds

Philipp Post

[Erland Sommarskog]

(st******@gmail.com) writes:

I'm trying to locate duplicate data in a table using 4 columns:
employee_id (primary key), employeeid, lastname and firstname.

I can pull up the duplicate data with the last three listed columns,
but when I include the first column (employee_id) in the statement, it
shows nothing, since the employee_id value is unique for each row.
Here's the statement I'm currently using:

It seems that that employee_id column should not be there, but there
should be a real key in the table.

select employeeid, firstname, lastname
from empmain
group by employeeid, firstname, lastname
having count(*) 1

The only thing I need extra from this statement is a separate row for
each duplicate value, showing the unique employee_id value. I know I
need to take the Group By option out, but I get the error "Column
'empmain.employeeid' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.".
Tried doing research on this issue, but honestly, my knowledge of SQL
is limited and I'm really not sure I'm asking the right questions when
searching. Any help would be appreciated.

On SQL 2005 you can do:

WITH counts AS (
SELECT employee_id, employeeid, firstname, lastname,
cnt = COUNT(*)
OVER (PARTITION BY employeeid, firstname, lastname)
FROM empmain
)
SELECT employee_id, employeeid, firstname, lastname
FROM counts
WHERE cnt 1

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx