To find duplicate entries from 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

SQL Server 2005 Forums

Transact-SQL (2005)

To find duplicate entries from table

Author

Topic

ersantosh1
Starting Member

6 Posts

Posted - 2012-09-07 : 02:47:48

Hi,
I have MST_NOTES table with following 6 columns from i have to find duplicate entries:-

NT_ID | NT_PERS_ID | NT_SUB | NT_NOTES | NT_USER_ID | NT_NOTES_TO
1 | 40 | subject | my notes | 1234 | mr. visitor
2 | 40 | subject | my notes | 1234 | mr. visitor
3 | 40 | new | my notes | 1234 | mr. visitor
4 | 41 | sub | my nte 1 | 1234 | mr. visitor
5 | 41 | sub | my no 1 | 1234 | mr. visitor
6 | 41 | new | my nte 1 | 1234 | mr. visitor

from above table a want to delete only record with NT_ID=1 or 2 and 4 or 6.

can i get this kind of sql query????
Plz Help me in that..

Thanks and regards,
santosh

srahangdale

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-09-07 : 04:12:16

select
NT_PERS_ID, NT_SUB, NT_NOTES, NT_USER_ID, NT_NOTES_TO
from
MST_NOTES
group by
NT_PERS_ID, NT_SUB, NT_NOTES, NT_USER_ID, NT_NOTES_TO
having count(*) > 1

That will get you the duplicates, then all you need to do is turn this in to a self join and get the min (or max, your choice) NT_ID and that will be the ones you need to delete.

ersantosh1
Starting Member

6 Posts

Posted - 2012-09-07 : 05:44:10

Thank u so much sir, I really appriciate this. :)

srahangdale

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-09-07 : 10:59:38

;
With CTE
AS ( select NT_PERS_ID, NT_SUB, NT_NOTES, NT_USER_ID, NT_NOTES_TO,
ROW_NUMBER() OVER ( PARTITION BY NT_PERS_ID, NT_SUB,
NT_NOTES, NT_USER_ID, NT_NOTES_TO )
AS ROW_NUM
from MST_NOTES)
DELETE FROM CTE
WHERE ROW_NUM > 1

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources