Check Dupplicate entries in table

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

Check Dupplicate entries in table

Author

Topic

roykauf
Starting Member

36 Posts

Posted - 2008-02-26 : 05:50:56

Helo everybody,

How can i find a dupplicate entry in one table?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 05:53:28

Check this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Even though the topic is to delete duplicates, you can easily understand the technique and apply it to finding duplicates.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 05:53:47

Can you be more specific please? Please provide some sample data from your table and o/p you desire to help the people solve your problem. Thanks.

roykauf
Starting Member

36 Posts

Posted - 2008-02-26 : 06:29:26

Thank for you reply.
I don't need to Delete the Dupplicate entries , I have to find them. for example:
i have a table with these columns:
[time] - datetime,
ResellerSubnetID - bigint,
inputAmount - money
Remark - varchar

one row shloud look like this:
2008-01-10 13:10:00 Reseller1 1000.0000 'Remark number 1'
I Have to find if there is a row like this in the table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 06:38:42

If its sql 2005

SELECT t.[time],
t.ResellerSubnetID,
t.inputAmount,
t.Remark

FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY ResellerSubnetID ORDER BY ResellerSubnetID) AS RowNo,
[time],
ResellerSubnetID,
inputAmount,
Remark
FROM Table
)t
WHERE t.RowNo<>1

EDIT: Sorry wrongly interpreted as delete earlier

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-02-26 : 07:23:55

SELECT A.[TIME],A.ResellerSubnetID,A.inputAmount,A.REMARK FROM Table A
CROSS JOIN Table B
WHERE A.[TIME]=B.[TIME]
AND A.ResellerSubnetID=b.ResellerSubnetID
AND A.inputAmount=B.inputAmount
AND A.REMARK=B.REMARK
GROUP BY A.[TIME],A.ResellerSubnetID,A.inputAmount,A.REMARK
HAVING COUNT(*) >1

This might not be very optimal but will give the right result. Please let me know if this is wrong:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 07:29:28

quote:
Originally posted by pravin14u

SELECT A.[TIME],A.ResellerSubnetID,A.inputAmount,A.REMARK FROM Table A
CROSS JOIN Table B
WHERE A.[TIME]=B.[TIME]
AND A.ResellerSubnetID=b.ResellerSubnetID
AND A.inputAmount=B.inputAmount
AND A.REMARK=B.REMARK
GROUP BY A.[TIME],A.ResellerSubnetID,A.inputAmount,A.REMARK
HAVING COUNT(*) >1

This might not be very optimal but will give the right result. Please let me know if this is wrong:)

This will give all the records that have duplicate entries. I thought OP had asked only to list duplicate ones (excluding main entry)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 07:30:07

quote:
Originally posted by roykauf

Thank for you reply.
I don't need to Delete the Dupplicate entries , I have to find them. for example:
i have a table with these columns:
[time] - datetime,
ResellerSubnetID - bigint,
inputAmount - money
Remark - varchar

one row shloud look like this:
2008-01-10 13:10:00 Reseller1 1000.0000 'Remark number 1'
I Have to find if there is a row like this in the table

If you dont want to delete duplicate entries, then vishak's query to

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY ResellerSubnetID ORDER BY ResellerSubnetID) AS RowNo,
[time],
ResellerSubnetID,
inputAmount,
Remark
FROM Table
)t
WHERE t.RowNo<>1

Provided you use SQL Server 2005 or higher version

Madhivanan

Failing to plan is Planning to fail

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-02-26 : 07:36:18

quote:
Originally posted by roykauf

Thank for you reply.
I don't need to Delete the Dupplicate entries , I have to find them. for example:
i have a table with these columns:
[time] - datetime,
ResellerSubnetID - bigint,
inputAmount - money
Remark - varchar

one row shloud look like this:
2008-01-10 13:10:00 Reseller1 1000.0000 'Remark number 1'
I Have to find if there is a row like this in the table

I think the following will do, for just FINDING:

SELECT * FROM yourTABLE
GROUP BY [TIME],ResellerSubnetID,inputAmount,REMARK
HAVING COUNT(*)>1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 07:40:12

quote:
Originally posted by pravin14u

quote:
Originally posted by roykauf

Thank for you reply.
I don't need to Delete the Dupplicate entries , I have to find them. for example:
i have a table with these columns:
[time] - datetime,
ResellerSubnetID - bigint,
inputAmount - money
Remark - varchar

one row shloud look like this:
2008-01-10 13:10:00 Reseller1 1000.0000 'Remark number 1'
I Have to find if there is a row like this in the table

I think the following will do, for just FINDING:

SELECT * FROM yourTABLE
GROUP BY [TIME],ResellerSubnetID,inputAmount,REMARK
HAVING COUNT(*)>1

It is always good practice to explicitely name the columns if group by clause is included

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources