473,699 Members | 2,488 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Checking for duplicates in a database table

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.employ eeid' 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
Jun 27 '08 #1
3 2532
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

Jun 27 '08 #2
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
Jun 27 '08 #3
(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.employ eeid' 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****@sommarsk og.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
Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1199
by: keys4worship | last post by:
Situation: Day 1 Table contains 100 items of actions imported via FTP. One of the fields in the table can be updated to reflect an assigned unit code. Day 2 Actions that may be duplicates of the table from Day 1 (with the exception of the updated assigned unit field) are imported for inclusion in the Table from Day 1.
4
3624
by: Drew | last post by:
I have a permission tracking app that I am working on, and I have made the insert page for it. I am having issues on how to prevent duplicates from getting entered. Currently the interface for the app has a mixture of select boxes, list boxes and checkboxes. The form submits the page to processAIMR.asp and then does the inserting. I am using a loop to insert a new record for each checkbox checked or listbox entry selected. My...
5
1900
by: Jermin | last post by:
Hi, I have a database composed of a single table composed of 2 columns, an auto-numbered ID column and a column which contains 30 million random numbers. All I want to Access to do is check the numbers and let me know if there are any duplicates. Ideally I'd like Access to flag the duplicates if they exist. The easy approach that I thought would work is to go to Design view and select Indexed table, No Duplicates. This doesn't work...
1
1667
by: MHenry | last post by:
Hi, I have a table with duplicate records. Some of the duplicates need to be eliminated from the table and some need not. A duplicate record does not need to be eliminated if the one record appears multiple times on one employee's report. However, if the same record(s) appear on any other employee's report, it means they collaborated on the item, and the item from the second employee's report should be eliminated from the table.
6
2398
by: Marlene | last post by:
Hi All I have the following scenario, where I have found all the duplicates in a table, based on an order number and a part number (item).I might have something like this: Order PODate Rec Qty Invoice# Item Supplier Status POReceivedDate 570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004 570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004 570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004
6
2890
by: Maxi | last post by:
I have 100 tabes in an Access database, every table has 1 filed with 100 names (records), no primary key assigned. I would like to find duplicates. Here is the criteria: The computer should pick up the first name of Table1 and check that name in that table (Table1) as well as the remaining 99 tables. Continue this till we reach the last name (record) of the 100th table.
3
2169
by: AK | last post by:
Hi Our product uses MS-SQL Server 2000. One of our customer has 10 installations with each installation stroring data in its own database. Now the customer wants to consolidate these databases into one and we already have plan for that by consolidating one DB at a time. But first they want to find how many unique or duplicate entries they have across all the 10 databases Assumptions:
16
3501
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...
2
1400
by: lostdawg | last post by:
Hi, I have a question regarding duplicate entries. Basically I have a simple database with a main table called contacts. This table has 2 fields, contactsid and contactsnum. The main form has a combo box select which triggers a subform query to display records that are relevant sorting via the contactsid field. I would like to be able to prevent duplicates being entered in the contactsnum field for each group of contactsid displayed...
7
6858
by: john.cole | last post by:
I have searched all the groups I can, and I still haven't been able to come up the solution I need. I have the following problem. In my form named sbfrmSpoolList, I am entering a job, spool and revision number. My table is indexed properly to not allow duplicates, but I would like teh user to be notified that they are typing a duplicate via a message box, then I woulld the update of the record to be cancelled. I have tried the...
0
8615
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9174
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8914
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7750
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6534
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4376
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3057
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2347
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2009
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.