I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness]; GO CREATE TABLE [dbo].[TestBusiness]( [Business_pk] INT IDENTITY(1,1) NOT NULL, [BusinessName] VARCHAR (200) NOT NULL, [Address] VARCHAR(MAX) NOT NULL,
I have a dilema..... I have a databas eof about 60,000 users and i need to get rid of those users where there is a duplicate email address. I have written an asp utilty that works but is far too taxing on our little server and i thinkk itwill kill it. what it does is for each email address it compares it against all the others.... so for each address it checks against 60,000 other records 60,000 times.... you know what i mean. its pretty phucked.... i tested it on just one record and took about 5mins.
anyway ive been trying to do it in SQL with no luck
i'm trying to get duplicates out of the my database
SELECT COUNT(*) AS Amount, Firstname, surname, Internalextension FROM iac.dbo.sf_profil GROUP BY FirstName, surname, internalextension HAVING COUNT(*) > 1 order by firstname, surname
How do i alter the query just retrieve records which have firstname and lastname which are similar but different extension numbers ?
Hi, This is the query which shows me the duplicates Some of the records have more than one records I would like to know how to delete the extra records so that I will end up with one record per row.
select Pricing_Source, VaR_Identifier, Price_Date, PX_Last, Count(*) as 'count' from tblPricesClean group by Pricing_Source, VaR_Identifier, Price_Date, PX_Last having count(*) > 1 order by count desc
Is there a way to find duplicates in one field? For example my query has person_nbr and for each person_nbr on one day they could have used multiple payer_names. I want to be able to count each person_nbr one time but also I want to group by description(which is the name of the provider) and by payer name to see how many person's that the provider seen with each payer. My problem is that if the person had more than one payer they are counted twice. Is there some type of aggregate function to use the first payer in the list??
With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as ( select distinct person_id,person_nbr,first_name,last_name,date_of_birth from (select count(*) as countenc,a.person_id,a.person_nbr, a.first_name,a.last_name, a.date_of_birth from person a join patient_encounter b on a.person_id = b.person_id group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp where tmp.countenc <=1 ) select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp from ( select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name, tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp from PersonMIA tmp join person a on a.person_id = tmp.person_id join patient_encounter b on a.person_id = b.person_id join provider_mstr c on b.rendering_provider_id = c.provider_id join person_payer d on tmp.person_id = d.person_id where c.description = 'Leon MD, Enrique' group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name, tmp.date_of_birth,d.payer_name,b.create_timestamp )tmp2 where year(create_timestamp) IN (2005,2006) group by person_nbr,payer_name,first_name,last_name,description,create_timestamp
Hi, I'll see if I can explain this clearly. The query below selects rows from the "hdr_ctl_nbr_status" table if the value in the field "tcn" from that table is found in the table "temp_tcn". I want all fields from the "hdr_ctl_nbr_status" table to be selected BUT only one row. In other words for a tcn with a value "12345678" there are 10 rows returned from the hdr_ctl_nbr_status table, I want only 1. Is there a way I can use SELECT DISTINCT to do this ? I know this usually functions on one or more fields but I want the DISTINCT to be on tcn only BUT return all fields in the query.
Select h.*,'' from hdr_ctl_nbr_status as h WITH (NOLOCK) where h.tcn in (select tcn from temp_tcn)
I have two columns of int data in the a table, as my example data shows below.
I want my data returned to be something like those in #test3, but my question is this, how can I do it without using #test2 and #test3?
By the way, the business requirement doesn't care it's min/max or any ID when one side has duplicated values.
Thanks!
Use tempdb Go
if object_ID ('#test') is not null drop table #test
create table #test (col1 int, col2 int) insert into #test Select 123, 222 union Select 124, 222 union Select 125, 222 union Select 111, 223 union Select 111, 224
if object_ID ('#test2') is not null drop table #test2 create table #test2 (col1 int, col2 int) Insert into #test2 Select distinct col1, min(col2) from #test group by col1
if object_ID ('#test3') is not null drop table #test3 create table #test3 (col1 int, col2 int) Insert into #test3 Select min(col1), col2 from #test2 group by col2
I am attempting to execute the Stored Procedure at the foot of thismessage. The Stored Procedure runs correctly about 1550 times, butreceive the following error three times:Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,Line 68Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I've done some digging, and the error message is moderatelyself-explanatory.The problem is that there is no Line 68 in the Stored Procedure. It'sthe comment line:-- Need to find out how many hours the employee is scheduled etc.Also, there are no duplicate records in the Employee table nor theWeeklyProfile table. At least I assume so - if the following SQL todetect duplicates is correct!SELECT E.*FROMEmployee Ejoin(select EmployeeIDfromEmployeeGroup by EmployeeIDhaving count(*) > 1) as E2On(E.EmployeeID = E2.EmployeeID)SELECTW.*FROMWeekProfile Wjoin(SelectWeekProfileIDFROMWeekProfileGROUP BYEmployeeID, MondayHours, WeekProfileIDHAVING COUNT(*) > 1) AS W2ONW.WeekProfileID = W2.WeekProfileIDNOTE: In the second statement, I have tried for MondayHours thruFridayHours.Anyone got any ideas? The TableDefs are set up in this thread:<http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/fff4ef21e9964ab8/f5ce136923ebffc3?q=teddysnips&rnum=1&hl=en#f5ce136923ebffc3>The Stored Procedure that causes the error is here:--************************************************** ***********CREATE PROCEDURE BackFillNetworkHoursASDECLARE @EmployeeID intDECLARE @TimesheetDate DateTimeDECLARE @NumMinutes intDECLARE @NetworkCode int-- Get the WorkID corresponding to Project Code 2002SELECT@NetworkCode = WorkIDFROM[Work]WHERE(WorkCode = '2002')-- Open a cursor on a SELECT for all Network Support Employees whereany single workday comprises fewer than 7.5 hoursDECLARE TooFewHours CURSOR FORSELECTEmployeeID,CONVERT(CHAR(8), Start, 112) AS TimesheetDate,SUM(NumMins) AS TotalMinsFROM(SELECTTI.EmployeeID,W.WorkCode,TI.Start AS Start,SUM(TI.DurationMins) AS NumMinsFROMTimesheetItem TI LEFT JOIN[Work] W ON TI.WorkID = W.WorkIDWHERE EXISTS(SELECT*FROMEmployee EWHERE((TI.EmployeeID = E.EmployeeID) AND(E.DepartmentID = 2)))GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS xGROUP BYEmployeeID,CONVERT(char(8), Start, 112)HAVINGSUM(NumMins) < 450ORDER BYEmployeeID,CONVERT(CHAR(8), Start, 112)-- Get the EmployeeID, Date and Number of Minutes from the cursorOPEN TooFewHoursFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesWHILE (@@FETCH_STATUS=0)BEGINDECLARE @NewWorkTime datetimeDECLARE @TimesheetString varchar(50)DECLARE @Duration intDECLARE @RequiredDuration int-- Set the correct date to 08:30 - by default the cast from thecursor's select statement is middaySET @TimesheetString = @TimesheetDate + ' 08:30'SET @NewWorkTime = CAST(@TimesheetString AS Datetime)-- Need to find out how many hours the employee is scheduled to workthat day.SET @RequiredDuration = CASE (DATEPART(dw, @NewWorkTime))WHEN 1 THEN(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 2 THEN(SELECT CAST((60 * MondayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 3 THEN(SELECT CAST((60 * TuesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 4 THEN(SELECT CAST((60 * WednesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 5 THEN(SELECT CAST((60 * ThursdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 6 THEN(SELECT CAST((60 * FridayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 7 THEN(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))ENDIF @NumMinutes < @RequiredDurationBEGIN-- Set the Start for the dummy work block to 08:30 + the number ofminutes the employee has already worked that daySET @NewWorkTime = DateAdd(minute, @NumMinutes, @NewWorkTime)-- Set the duration for the dummy work block to be required durationless the amount they've already workedSET @Duration = @RequiredDuration - @NumMinutes-- Now we have the correct data - insert into table.INSERT INTO TimesheetItem(EmployeeID,Start,DurationMins,WorkID)VALUES(@EmployeeID,@NewWorkTime,@Duration,@NetworkCode)ENDFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesENDCLOSE TooFewHoursDEALLOCATE TooFewHoursGO--************************************************** ***********ThanksEdward
I have a table, TEST_TABLE, with 6 columns (COL1, COL2, COL3, COL4,COL5, COL6).... I need to be able to select all columns/rows whereCOL3, COL4, and COL5 are unique....I have tried using DISTINCT and GROUP BY, but both will only allow meto access columns COL3, COL4, and COL5..... i need access to allcolumns...I just want to get rid of duplicate rows (duplicates ofCOL3, COL4, and COL5)...Thanks in advance.Joe
Hello! Just looking for advise on dealing with duplicates in database. I have a contact table that have a bunch of duplicated customer records. My goal is to combine all duplicated records into one record. This involves couple tables:contact,contact history ,calendar. All tables related by common column "accountno". What would be the best approach for this?
I have a customer database with the following structure:
Dept ID (int) Section (varchar)
I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.
I’m using SQL Server 2000. I have a table called Contacts and I would like to be able to have the UserID as an indexed column and to ignore duplicates. I set up the following properties within my SQL Server database table:
Every time I try to enter duplicates for the UserID column; I get an error that says, “Cannot enter duplicate key row in object ‘Contacts’. Can anyone explain this? Is it possible to create an index column with duplicate data?
I have a table with 100,000 plus records in it, and some are duplicates. Is there any way to delete one of them and not the other. For instance, if I duplicate the table I could run this query. <cfquery name="query1" datasource="datasource"> DELETE DISTINCT FROM tablename WHERE FirstName in ( SELECT FirstName from tablename1 where tablename1.FirstName = tablename.FIRST_NAME AND tablename1.LastName = tablename.LAST_NAME AND tablename1.State = tablename.STATE) </cfquery>
However, it doesn't work. I know the distinct is not correct. But does anyone know how to achieve this, I have looked all over, and everything I try deletes both records. I was thinking of using some kindof count statement, but it still deletes both of them. Please help. Thanks
I have a members table and have added an extra few thoushand members to it. Now I need to remove the duplicates.
It doesnt matter which duplicate i remove as long as there are unique email addresses.
so here is the format of the table:
id email firstname lastname datebirth
if i do a:
SELECT COUNT(DISTINCT Email) AS Expr1 FROM Customer
it returns 21345
and
SELECT Count(Email) FROM Customer
returns 28987
I can get the unique email addresses into another table by going:
SELECT DISTINCT emailaddress INTO DistinctCustomer FROM Customer
but this will only return unique email addresses. How do i select distinct email address and all other fields into a new table? or just remove duplicates where email address appears more then once?
I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)
thank you
SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups FROM TERMINATION GROUP BY [TM#] HAVING Count([TM#])>1; GO
Can some kind person out there please help me, I've been stuck on this for daaaa-y-s.
I have a database that allows users to search for pdf's of technical drawings.
Basically I have one huge table with multiple columns, which the user can only search on any combination of one of these two columns "drawing_series" eg 0100, 0046, 1000 "drawing_number" eg 0076000, 0000123, 0000004
There is also a Revision column(which the user can't see) that goes up by 1 each time a drawing has been modified and resubmitted to the database. "revision" eg 01, 02, 03, ....... 99
So a search on 0046 series might pull back drawings
The problem is that I only want drawings with the highest revisions returned eg
0046-0010000-03 0046-0076000-01 0046-0076888-02
The code below worked like a charm in the test stages pulling back a few hundred records but now that i've uploaded 10's of thousands of records to the DB the whole lot dies if the search result pulls back more than a few thousand records.
SELECT * FROM dbo.Drawing_Database where dbo.Drawing_Database.revision=(select max(revision) from dbo.Drawing_Database self where self.drawing_series + self.drawing_number = dbo.Drawing_Database.drawing_series + dbo.Drawing_Database.drawing_number) Drawing_Series like '0046' order by Drawing_Series, Drawing_Number
There must be a simpler way of doing this as i can pull out duplicate series + numbers using " HAVING Count(*)>1" but dont know where to go from there.
Have a pretty simple wuestion but the answer seems to be evading me:
Here's the DDL for the tables in question:
CREATE TABLE [dbo].[Office] ( [OfficeID] [int] IDENTITY (1, 1) NOT NULL , [ParentOfficeID] [int] NOT NULL , [WebSiteID] [int] NOT NULL , [IsDisplayOnWeb] [bit] NOT NULL , [IsDisplayOnAdmin] [bit] NOT NULL , [OfficeStatus] [char] (1) NOT NULL , [DisplayORD] [smallint] NOT NULL , [OfficeTYPE] [varchar] (10) NOT NULL , [OfficeNM] [varchar] (50) NOT NULL , [OfficeDisplayNM] [varchar] (50) NOT NULL , [OfficeADDR1] [varchar] (50) NOT NULL , [OfficeADDR2] [varchar] (50) NOT NULL , [OfficeCityNM] [varchar] (50) NOT NULL , [OfficeStateCD] [char] (2) NOT NULL , [OfficePostalCD] [varchar] (15) NOT NULL , [OfficeIMG] [varchar] (100) NOT NULL , [OfficeIMGPath] [varchar] (100) NOT NULL , [RegionID] [int] NOT NULL , [OfficeTourURL] [varchar] (255) NULL , [GeoAreaID] [int] NOT NULL , [CreateDT] [datetime] NOT NULL , [UpdateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [UpdateByID] [varchar] (50) NOT NULL , [OfficeBrandedURL] [varchar] (255) NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[OfficeManagement] ( [OfficeID] [int] NOT NULL , [PersonnelID] [int] NOT NULL , [JobTitleID] [int] NOT NULL , [CreateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [SeqNBR] [int] NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[OfficeMls] ( [OfficeID] [int] NOT NULL , [SourceID] [int] NOT NULL , [OfficeMlsNBR] [varchar] (20) NOT NULL , [CreateDT] [datetime] NOT NULL , [UpdateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [UpdateByID] [varchar] (50) NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[Personnel] ( [PersonnelID] [int] IDENTITY (1, 1) NOT NULL , [PersonnelDisplayName] [varchar] (100) NOT NULL , [FirstNM] [varchar] (50) NOT NULL , [PreferredFirstNM] [varchar] (50) NOT NULL , [MiddleNM] [varchar] (50) NOT NULL , [LastNM] [varchar] (50) NOT NULL , [PersonalTaxID] [varchar] (9) NOT NULL , [HireDT] [datetime] NOT NULL , [TermDT] [datetime] NOT NULL , [HomePhoneNBR] [varchar] (15) NULL , [HomeADDR1] [varchar] (50) NOT NULL , [HomeADDR2] [varchar] (50) NOT NULL , [HomeCityNM] [varchar] (50) NOT NULL , [HomeStateCD] [char] (2) NOT NULL , [HomePostalCD] [varchar] (15) NOT NULL , [PersonnelLangCSV] [varchar] (500) NOT NULL , [PersonnelSlogan] [varchar] (500) NOT NULL , [BGColor] [varchar] (50) NOT NULL , [IsEAgent] [bit] NOT NULL , [IsArchAgent] [bit] NOT NULL , [IsOptOut] [bit] NOT NULL , [IsDispOnlyPrefFirstNM] [bit] NOT NULL , [IsHideMyListingLink] [bit] NOT NULL , [IsPreviewsSpecialist] [bit] NOT NULL , [AudioFileNM] [varchar] (100) NULL , [iProviderID] [int] NOT NULL , [DRENumber] [varchar] (10) NOT NULL , [AgentBrandedURL] [varchar] (255) NOT NULL , [CreateDT] [datetime] NOT NULL , [UpdateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [UpdateByID] [varchar] (50) NOT NULL , [IsDisplayAwards] [bit] NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[PersonnelMLS] ( [PersonnelID] [int] NOT NULL , [SourceID] [int] NOT NULL , [AgentMlsNBR] [varchar] (20) NOT NULL , [CreateDT] [datetime] NOT NULL , [UpdateDT] [datetime] NOT NULL , [CreateByID] [varchar] (50) NOT NULL , [UpdateByID] [varchar] (50) NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Office] ADD CONSTRAINT [FK_Office_OfficeProfile] FOREIGN KEY ( [OfficeID] ) REFERENCES [dbo].[OfficeProfile] ( [OfficeID] ) NOT FOR REPLICATION GO
alter table [dbo].[Office] nocheck constraint [FK_Office_OfficeProfile] GO
ALTER TABLE [dbo].[OfficeManagement] ADD CONSTRAINT [FK_OfficeManagement_LookupJobTitle] FOREIGN KEY ( [JobTitleID] ) REFERENCES [dbo].[LookupJobTitle] ( [JobTitleID] ), CONSTRAINT [FK_OfficeManagement_Office] FOREIGN KEY ( [OfficeID] ) REFERENCES [dbo].[Office] ( [OfficeID] ) NOT FOR REPLICATION , CONSTRAINT [FK_OfficeManagement_Personnel] FOREIGN KEY ( [PersonnelID] ) REFERENCES [dbo].[Personnel] ( [PersonnelID] ) ON DELETE CASCADE GO
alter table [dbo].[OfficeManagement] nocheck constraint [FK_OfficeManagement_Office] GO
ALTER TABLE [dbo].[OfficeMls] ADD CONSTRAINT [FK_OfficeMls_Office] FOREIGN KEY ( [OfficeID] ) REFERENCES [dbo].[Office] ( [OfficeID] ) NOT FOR REPLICATION GO
alter table [dbo].[OfficeMls] nocheck constraint [FK_OfficeMls_Office] GO
ALTER TABLE [dbo].[PersonnelMLS] ADD CONSTRAINT [FK_PersonnelMLS_Personnel] FOREIGN KEY ( [PersonnelID] ) REFERENCES [dbo].[Personnel] ( [PersonnelID] ) NOT FOR REPLICATION GO
alter table [dbo].[PersonnelMLS] nocheck constraint [FK_PersonnelMLS_Personnel] GO
Here's the query I'm having trouble with:
SELECT distinct Personnel.PersonnelID, Personnel.FirstNM, Personnel.LastNM, Office.OfficeNM, Office.OfficeID, OfficeMls.SourceID AS OfficeBoard, PersonnelMLS.SourceID AS AgentBoard FROM Personnel INNER JOIN OfficeManagement ON Personnel.PersonnelID = OfficeManagement.PersonnelID INNER JOIN Office ON OfficeManagement.OfficeID = Office.OfficeID INNER JOIN OfficeMls ON Office.OfficeID = OfficeMls.OfficeID INNER JOIN PersonnelMLS ON Personnel.PersonnelID = PersonnelMLS.PersonnelID where officemls.sourceid <> personnelmls.sourceid and office.officenm not like ('%admin%') group by PersonnelMLS.SourceID, Personnel.PersonnelID, Personnel.FirstNM, Personnel.LastNM, Office.OfficeNM, Office.OfficeID, OfficeMls.SourceID order by office.officenm
What I'm trying to retrieve are those agents who have source id's that are not in the Office's domain of valid source id's. Here's a small portion of the results:
PersonnelID FirstNM LastNM OfficeNM OfficeID OfficeBoard AgentBoard ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- ----------- 18205 Margaret Peggy Quattro Aventura North 650 906 908 18205 Margaret Peggy Quattro Aventura North 650 918 908 15503 Susan Jordan Blackburn Point 889 920 909 15503 Susan Jordan Blackburn Point 889 921 909 15503 Susan Jordan Blackburn Point 889 921 920 15279 Sandra Humphrey Boca Beach North 890 917 906 15279 Sandra Humphrey Boca Beach North 890 906 917 15279 Sandra Humphrey Boca Beaches 626 917 906 15279 Sandra Humphrey Boca Beaches 626 906 917 13532 Michael Demcho Boca Downtown 735 906 917 14133 Maria Ford Boca Downtown 735 906 917 19126 Michael Silverman Boca Glades Road 736 917 906 18920 Beth Schwartz Boca Glades Road 736 906 917
If you take a look at Sandra Humphries, you'll see she's out of office 626. Office 626 is associated with source id's 907 and 916. Sandra Humphries is also associated with those two source id's , but she shows up in the results.
I know this was AWFULLY long winded, but just wanted to make sure made myself as clear as possible.
Just wanted to ask how to get all the duplicates records in a table. If I have say the following: col1 col2 col3 col4 col5 1 A1 ABC A21 AJ 1 A1 ABC A21 AJ 1 A2 ABC A21 AJ
The query should return the first 2 identical rows. I tried the following form but as you can see it has flaw that it gets the 3rd row as well simply because the outer select uses col1 as a condition which could belong to a "not completely" identical row. The inner select results in the distinct duplicate rows (2 in the table above, either of row 1 or 2 and row 3).
select * from table1 where col1 IN (select col1 from table1 group by col1, col2, col3, col4, col5 having count(*) > 1 )
I am quite upto the limit now. My query below shows duplicates (I mean the drivers) evenif I group them -
select ltrim(rtrim(supplier.name)) Company, c.fore, c.name, count(c.linkid) [Total Responses], CASE WHEN round(AVG(CONVERT(float(3), csr.driver_comm)), 1) > 10 THEN 10 ELSE round(AVG(CONVERT(float(3), csr.driver_comm)), 1) END AS [Driver Communication]
from supplier inner join contact c on supplier.id = c.linkid inner join csr on csr.tourcode = supplier.code inner join booking on csr.bin = booking.id
where supplier.tid = 301 group by supplier.name, csr.bin, c.name, c.fore order by 1
Output:
A L S Travel.....DavidDraper.....4.....7 A L S Travel.....AndrewScott.....16.....10 A L S Travel.....KeathK.....8.....10 A L S Travel.....DavidDraper.....8.....8.5 A L S Travel.....KeathK.....4.....10 A L S Travel.....PaulHammond.....4.....7 A L S Travel.....YanFaczynski.....8.....7 A L S Travel.....YanFaczynski.....4.....10 A L S Travel.....JohnGay.....8.....10 A L S Travel.....YanFaczynski.....12.....3
I was just wondering if any one knows how to find duplicate keys using more than one field. I used the below key to find those people who exists in list1 but don't exists in list2. I realized that the results had some duplicates which was expected but how do I then find all those duplicate people. I know how to do it if there was a primary key present I would have done a count (distinct cardnumber) > 1 and i would have done the select statement like this distinct cardnumber, but how do I do it with more that one key??
