REMOVING THE DUPLICATE ROWS

1.FIRST WE CREATE THE TABLE

CREATE TABLE [dbo].[ATTENDANCE](
[EMPLOYEE_ID] [varchar](50) NOT NULL,
[ATTENDANCE_DATE] [date] NOT NULL) ON [PRIMARY]

2.WE CAN INSERT DATA INTO TABLE WITH DUPLICATE DATA

INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A003',CONVERT(DATETIME,'01-01-11',5))

SELECT * FROM ATTENDANCE

EMPLOYEE_ID                            ATTENDANCE_DATE
--------------------------------------------- ---------------
A001                                               2011-01-01
A001                                               2011-01-01
A002                                               2011-01-01
A002                                               2011-01-01
A002                                               2011-01-01
A003                                               2011-01-01

3.NOW WE CAN ADD A COLUMN TO TABLE WITH IDENTITY

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)

4.NOW WE CAN SEE WHAT ARE THE DUPLICATE ROWS

SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

EMPLOYEE_ID                                        ATTENDANCE_DATE AUTOID
-------------------------------------------------- --------------- -----------
A001                                               2011-01-01      2
A002                                               2011-01-01      4
A002                                               2011-01-01      5


5.THE QUERY FOR REMOVING THE DUPLICATES

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

6.NOW WE CAN CHECK THE RESULT

SELECT * FROM dbo.ATTENDANCE

EMPLOYEE_ID                                        ATTENDANCE_DATE AUTOID
-------------------------------------------------- --------------- -----------
A001                                               2011-01-01      1
A002                                               2011-01-01      3
A003                                               2011-01-01      6

create table EmpDup(empid int,name varchar(20))
   
    insert into EmpDup values(1,'Andy')
    insert into EmpDup values (1,'Andy')
    insert into EmpDup values(2,'Bill')
    insert into EmpDup values(2,'Bill')
    insert into EmpDup values (2,'Bill')
    insert into EmpDup values (3,'Chris')

select * from EmpDup

select distinct * into #tmp From EmpDup
    delete from EmpDup
    insert into EmpDup              
    select * from #tmp drop table #tmp

drop table EmpDup

alter table EmpDup add  sno int identity(1,1)

select * from EmpDup where sno not in (select min(sno) from EmpDup group by empid,name)

delete from EmpDup where sno not in (select min(sno) from EmpDup group by empid,name)

alter table EmpDup
    drop  column sno