Answer / mohamed ibrahim

Deleting multiple duplicate rows in a table
Ex . I have the Table named as TestMaster
to delete duplicate rows from the testmaster using Cursor &
RANK() Function.
for ex.the table having the fields ID,Name
the having the following data
oupput:
ID NAME
1 Raja
1 Raja
1 Raja
2 Mohamed
2 Mohamed
2 Mohamed

To Delete duplicate Rows in table to follow the below code:

DECLARE @ID INT
DECLARE delduplicaterecords_Cursor CURSOR
FOR SELECT ID FROM TempMaster
OPEN delduplicaterecords_Cursor
FETCH NEXT FROM delduplicaterecords_Cursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
WITH CTE
AS
(SELECT
ROW_NUMBER () OVER (ORDER BY ID) AS RowID,
*
FROM TempMaster WHERE ID=@ID )

DELETE FROM CTE WHERE RowID <> 1

FETCH NEXT FROM delduplicaterecords_Cursor INTO @ID
END

CLOSE delduplicaterecords_Cursor
DEALLOCATE delduplicaterecords_Cursor

Answer / sivam

sno sname salary
1 aaa 1000
1 aaa 1000
2 bbb 2000
2 bbb 2000
2 bbb 2000
1 aaa 1000

;with aa as
(
select sname,salary,ROW_NUMBER()over(partition by sno,sname,salary order by sno,sname,salary) as Nos from #testtable
)

delete from aa where Nos%2<>0

What is the difference between system objects and user objects?

0 Answers   HCL,

---

Tell me what is use of except clause? How it differs from not in clause?

0 Answers  

---

How to use union to merge outputs from two queries together in ms sql server?

0 Answers  

---

How to add code to the existing article (using improve article)?

0 Answers  

---

Tell me extended events in sql server 2008?

0 Answers  

---

Issues related in upgrading SQL Server 2000 to 2005 / 2008

0 Answers  

---

where do you use Isolations?give me some exmpale?

1 Answers  

---

Do you know what guidelines should be followed to help minimize deadlocks?

0 Answers  

---

What is view in sql?

0 Answers  

---

CREATE TABLE [dbo].[HPMS_CompetencyTypes](CompetencyType varchar(50) ) go create trigger hpms_create_Insert on HPMS_CompetencyTypes for insert as if Exists ( select * from [HPMS_CompetencyTypes] where CompetencyType=(select * from [HPMS_CompetencyTypes])) begin Rollback tran Raiserror ('duplicate value',12,6) go insert HPMS_CompetencyTypes (CompetencyType) values ('new') I'm new to trigger can any one tell me where is the issue. Please.

2 Answers  

---

how do we find every fifth record in a table

14 Answers   Aspire, HCL,

---

What is dknf in normalization form?

0 Answers  

---