how to find out the duplicate rows?

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

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

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

Nice post

SRILATHA
.Net Developer

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

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

Nice Explanation San.Pblr.Gct

Thanks & Regards
Hari

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

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

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