Answer / karna

delete from emp where empid not in(select max(empid) from
emp group by empname having count(*)>=1)

Answer / jiri

WITH DUPLICATE(EmpName, RowNumber)
AS
(SELECT EmpName,ROW_NUMBER() OVER (PARTITION BY EmpName
order by EmpName) AS RowNumber
FROM Employee)

DELETE FROM DUPLICATE WHERE RowNumber > 1

------------------------------------------------------------
Using CTE (Common Table Expressions) and ROW_NUMBER as
ranking fucntion


Jiri JANECEK
MSE, MBA, MCSD.NET

Answer / ambarish

we can use cursor.

Answer / venkat

Hope this methodology would help you better

1.Create a temp table
2.Select duplicated row's empid,empname into the temp table.
3.Create a cursor by selecting values from temp table.
4.Keep either min or max(empid) from original table and
delete the rest of the duplicated rows.

Answer / kumar

Table structure :-

empid empname
1 bala
2 bala
3 bala
4 bala
5 arun
6 arun
7 arun
8 ram
9 ram

Delete from employee where empid
not in (Select min(empid) from employee group by emp
having count(empid)>1)

By
Kumar

Answer / dinesh gupta

Kumar your query do not solve the purpose accurately.

It should be as

Delete from employee where empid
not in (Select min(empid) from employee group by empname
having count(empname)>=1)

Answer / dinesh gupta

use distinct commond

distinct(ename) from table name

What are different type of Collation Sensitivity?

0 Answers  

What is transaction server auto commit?

0 Answers  

i have table students with fields classname,studname select * from students classname studname 1 xxxxx 1 yyyy 1 zzzz 2 qqqq 2 tttt 3 dsds 3 www i want the output should be No of students in class 1 : 3 No of students in class 2 : 2 No of students in class 3 : 2

5 Answers   HCL, ZX,

Do you know what is fill factor and pad index?

0 Answers  

How to count groups returned with the group by clause in ms sql server?

0 Answers  

Some queries related to SQL

0 Answers   Motorola,

What is the syntax to execute the sys.dm_db_missing_index_details? : sql server database administration

0 Answers  

What is the difference between WHERE AND IN? OR 1. SELECT * FROM EMPLOYEE WHERE EMPID=123 2. SELECT * FROM EMPLOYEE WHERE EMPID IN (123) WHAT IS THE DIFFERENCE?

15 Answers   Adsys, Cap Gemini,

tell me what is blocking and how would you troubleshoot it? : Sql server database administration

0 Answers  

what is differencial backup?how to work?Anybody explai it?

2 Answers   HCL,

which query u can write to sql server doesn't work inbetween 7.00PM to nextday 9.00AM

5 Answers   Wipro,

Explain trigger and trigger types?

0 Answers