SQL FIND DUPLICATES IN TABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL FIND DUPLICATES IN TABLE

Author

Topic

DaveyB
Starting Member

10 Posts

Posted - 2008-05-28 : 08:01:55

I have this script bellow which does what it is supposed to. However it only outputs the cust_id. I want it to show all the columns in the table. How would I do this?


SELECT cust_id
FROM cust_table
WHERE cust_name in ('Billy','John') and rownum < 100
GROUP BY cust_id
HAVING COUNT(*) > 1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 08:08:46

SELECT * FROM cust_table
WHERE cust_id IN (SELECT cust_id
FROM cust_table
WHERE cust_name in ('Billy','John') and rownum < 100
GROUP BY cust_id
HAVING COUNT(*) > 1;
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-28 : 08:09:45

[code]SELECT ct.*
FROM Cust_Table AS ct
INNER JOIN (
SELECT Cust_ID
FROM Cust_Table
WHERE Cust_Name IN ('Billy', 'John')
AND RowNum < 100
GROUP BY Cust_ID
HAVING COUNT(*) > 1
) AS x ON x.Cust_ID = ct.Cust_ID
--WHERE RowNum < 100[/code]

E 12°55'05.25"
N 56°04'39.16"

DaveyB
Starting Member

10 Posts

Posted - 2008-05-28 : 08:39:00

quote:
Originally posted by visakh16

SELECT * FROM cust_table
WHERE cust_id IN (SELECT cust_id
FROM cust_table
WHERE cust_name in ('Billy','John') and rownum < 100
GROUP BY cust_id
HAVING COUNT(*) > 1;
)

quote:

SELECT ct.*
FROM Cust_Table AS ct
INNER JOIN (
SELECT Cust_ID
FROM Cust_Table
WHERE Cust_Name IN ('Billy', 'John')
AND RowNum < 100
GROUP BY Cust_ID
HAVING COUNT(*) > 1
) AS x ON x.Cust_ID = ct.Cust_ID
--WHERE RowNum < 100

For some reason this doesn't actually use my filter and over a 100 rows are returned. Why is this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-28 : 09:14:01

Maybe you should try to remove the outcommented "WHERE RowNum < 100" ?

You weren't exactly that clear in your requirements.
Yes we did understand that duplicates should only be found in the first 100 records.
But you never wrote which duplicates to return based on the latter information.

Try my sugggestion again and this time remove the "--" before the WHERE clause.

E 12°55'05.25"
N 56°04'39.16"

DaveyB
Starting Member

10 Posts

Posted - 2008-05-28 : 09:53:01

[/quote]
SELECT ct.*
FROM Cust_Table AS ct
INNER JOIN (
SELECT Cust_ID
FROM Cust_Table
WHERE Cust_Name IN ('Billy', 'John')
AND RowNum < 100
GROUP BY Cust_ID
HAVING COUNT(*) > 1
) AS x ON x.Cust_ID = ct.Cust_ID
WHERE Cust_Name IN ('Billy', 'John')
AND RowNum < 100
[/quote]

Great that works, however is there a more efficient way to do this? Maybe there isn't but it seems to really lag when i run this report off. :) - Thanks for all your help..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-28 : 15:28:15

If you make the code as a stored procedure it will run faster than as an ad-hoc query.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources