how to find out the duplicate rows?

Posted by Hemanth.Sutapalli under Sql Server on 8/14/2012 | Points: 10 | Views : 1015 | Status : [Member] | Replies : 8
hi
please help me to find the duplicate rows in my table , and displays all the duplicate rows not a particular row




Responses

Posted by: San.Pblr.Gct on: 8/14/2012 [Member] Starter | Points: 25

Up
0
Down
You have to give rownumber for each repeating rows and select duplicate rows by setting condition rn>1

Here is my sample.

CREATE TABLE [dbo].[EMptest](
[EMpCode] [varchar](10) NULL,
[Leave] [varchar](50) NULL,
[Remark] [varchar](100) NULL
)


my insert:
insert into emptest values('May001','CL','Urgent Work')
insert into emptest values('May001','EL','Holiday')
insert into emptest values('May001','CL','Urgent Work')
insert into emptest values('May001','EL','Holiday')
insert into emptest values('May002','CL','Urgent Work')



My query:
WITH CTE AS
(
SELECT *,RN=ROW_NUMBER() OVER (partition by empcode,leave,remark ORDER BY empcode DESC) FROM emptest
)
select a.empcode, a.leave,a.remark from cte a
where a.RN>1




Hemanth.Sutapalli, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hemanth.Sutapalli on: 8/14/2012 [Member] Starter | Points: 25

Up
0
Down
its nice but i am having a 10000 no of rows, if the row number creation of all this rows is difficult, and i want to insert a new record but we can see that repeated row numbers and then add so its difficult so can you explain without rownumber

Hemanth.Sutapalli, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: San.Pblr.Gct on: 8/14/2012 [Member] Starter | Points: 25

Up
0
Down
row number is not actual table column. While querying am using rownumber. and it doesnt matter whether u have 10 rows or 1 million rows, this rownumber will help you.

Can you let us know whats your actual function ? To identify duplicate rows? or to insert new row and check whther it doesnt exist already?

Hemanth.Sutapalli, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Srilu.Nayini577 on: 8/14/2012 [Member] Starter | Points: 25

Up
0
Down
Nice post

SRILATHA
.Net Developer

Hemanth.Sutapalli, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 8/14/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Kindly clarify the following

1. Have you used any Constraints on your Table ? (Primary Key, Foreign Key,...Etc.,)
2. Have you created any Indexes (Clustered/Nonclustered) ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Hemanth.Sutapalli, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 8/14/2012 [Member] Starter | Points: 25

Up
0
Down
Nice Explanation San.Pblr.Gct

Thanks & Regards
Hari

Hemanth.Sutapalli, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/11/2013 [Member] Starter | Points: 25

Up
0
Down


SELECT * FROM EMPLOYEE WHERE EMPID IN(SELECT EMPID FROM EMPLOYEE GROUP BY EMPID HAVING COUNT(*)<1)




sriram

Hemanth.Sutapalli, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 5/20/2013 [Member] Starter | Points: 25

Up
0
Down
http://www.aspnetcodes.com/2011/12/delete-duplicate-data.html

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Hemanth.Sutapalli, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response