SQL query to find duplicate records

[Guest_imported]

I am trying to write SQL to find duplicate records in an Approach database. the duplicate records are based on the project#, costcategory, lastname, and firstname all being the same (there are 6 more fields in the database). The sql i am using is
SELECT * FROM sample
GROUP BY project#, lastname, firstname
HAVING COUNT(project#)>1

This does not work.
Project# COUNT(project#)
145678 8
and the rest of the selected fields. I want to see all 8 of the records. Also I need to find the duplicates using the 3 other fields in this query. any help would be appreciated...the only sql i know is what i have taught myself in the past two days over the net....

 

[Andel]

can you give us sample table with data? and also can you give us a sample query result that you want.
Andel
maingel@hotmail.com

 

[allisong]

a sample of the table is:

project# category lastname firstname invoice#
123456 renovation Jones Jennifer 1234
234567 extension Smith Adam 9597
345678 renovation Jones Jennifer 7309


startdate enddate costcategory cheque# amount
01/01/00 17/03/00 travel 0001 100.00
22/04/00 04/05/00 salary 0002 247.97
15/02/00 15/03/00 travel 0003 987.00

This is all one table. I want to find duplicates based on project#, lastname, firstname, costcategory. The purpose of this database is to find double billings so I also need a query to find records that are duplicated and where the billing period overlaps. The result I would want from this query would be

project# category lastname firstname invoice#
123456 renovation Jones Jennifer 1234
345678 renovation Jones Jennifer 7309

startdate enddate costcategory cheque# amount
01/01/00 17/03/00 travel 0001 100.00
15/02/00 15/03/00 travel 0002 987.00

 

[jballum]

For the first one try something like this:

select
BB.project#,
BB.category,
BB.lastname,
BB.firstname,
AA.invoice#
from
(select
distinct A.project#,
A.category,
A.lastname,
A.firstname,
A.invoice#
FROM
sample) AA
inner join
(select
B.project#,
B.category,
B.lastname,
B.firstname
FROM
sample B
group by
B.project#,
B.category,
B.lastname,
B.firstname
having
count(*) > 1) BB
on AA.project# = BB.project# and
AA.category = BB.category and
AA.lastname = BB.lastname and
AA.firstname = BB.firstname


JB

 

[Andel]

One way to do this is to create a temp table first (we can call it #duplicates) to store only duplicate records based on project#, lastname, firstname, costcategory. Then do another select on your original table and the temp table. See example below:

SELECT project#, lastname, firstname, costcategory
INTO #duplicates
FROM myTable
GROUP BY project#, lastname, firstname, costcategory
HAVING COUNT (*) > 1
GO

SELECT * FROM #duplicates dups, myTable mt
WHERE dups.project# = mt.project# AND
dups.lastname = mt.lastname AND
dups.firstname = mt.firstname AND
dups.costcategory = mt.costcategory
GO

hope this helps....



Andel
maingel@hotmail.com

 

[Guest_imported]

Your query will not work because it is more or less like an aggregate query. What you need to get your duplicates, you need to create an alias for the table you are working on and match values in the fields you are interested in. This means that you can eliminate duplicates that are not duplicates by testing across multiple fields. Indeed, all your fields project#, costcategory, lastname, and firstname can be utilised. The dedup query is given below

select project#, costcategory, lastname, firstname from sample s
where (((project#) in
(select project# from sample as tmpSample
GROUP BY
project#,
costcategory,
lastname,
firstname
HAVING count(*)>1
AND project#=s.project#
AND costcategory=s.costcategory
AND lastname=s.lastname
AND firstname=s.firstname
)))
ORDER BY
project#,
costcategory,
lastname,
firstname
go


You will find that this query will work

 

[allisong]

Thank you very much guys, I was stuck on that query for a long time. I really appreciate the responses