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 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
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
(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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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.
|
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...
|
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...
|
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.
|
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
| |
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.
|
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:
|
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...
|
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...
|
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...
|
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,...
| |
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...
|
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,...
|
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...
|
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...
|
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...
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |