DB Duplicates

[curvv]

Hi,

How can I check for and count the number of duplicates in a Table?

thanx
chris ######## CtN ########

 

[derren]

Hi

You could do a "select distinct" on your table and compare the number of returned records to the number of records in the table. You could also try a "select ... group by ... having count(field)>1" to give you a list of all records which have duplicates. Derren
[Mediocre talent - spread really thin]

 

[link9]

sql query to pull dupes and the num:

SELECT COUNT(*), col1
FROM yourTable
GROUP BY col1
HAVING COUNT(*) > 1

Where col1 is the column you want to check for duplicates.


paul

 

[curvv]

Hi,

i get asp error:

Error Type:
(0x80020009)
Exception occurred.

my code:

sqlVerQ = "SELECT COUNT(*), Productid FROM product GROUP BY Productid HAVING COUNT(*) > 1"
set rsVer = objConnPr.execute(sqlVerQ)

response.write &quot;Number of Duplicate Records: &quot; & rsVer(0) & &quot;<p>&nbsp;</p>&quot;
######## CtN ########

 

[link9]

Try plugging that statement directly into your database, and see what it returns.

Also, that doesn't return a &quot;number of duplicate records&quot;.

It returns a dataset with counts and values of your duplicate records. Once you plug it straight into your database, you should see what I'm talking about.

 

[curvv]

ok, i understand now what values are being returned from the sql query, but how do i output any of those values?

my query:
SELECT COUNT(*),Category FROM product GROUP BY Category HAVING COUNT(*) > 1

the output i got was two columns:
XPR1000 and Category

with values
6 bla1
6 bla2

how can i now return those values and response.write there are 6 of bla1 and 6 of bla2???

thanx ######## CtN ########

 

[curvv]

don't worry, it works fine now. the reason i got the exception was because i checked the primary key for duplicates which cannot duplicate anyway.

thanx it works fine ######## CtN ########