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

select the 3rd maximum salary from sql server database if 4 (just an example In practically I may not know the exact situation) of the highest salaries are equal.

8 Answers   TCS,

what is raid? : Sql server database administration

0 Answers  

Get Current System Date Time

2 Answers  

What is the basic functions for master, msdb, tempdb databases?

2 Answers   CSC,

what is bit datatype and what's the information that can be stored inside a bit column? : Sql server database administration

0 Answers  

Selet all the data from table where last name is n of employee

2 Answers  

What is a transaction and what are ACID properties?

3 Answers  

What is the use of builtinadministrators group in sql server? : sql server security

0 Answers  

What is the difference between writing data to mirrored drives versus raid5 drives

0 Answers  

what is a stored procedure and trigger?

3 Answers  

How much space does sql server 2016 take?

0 Answers  

what operator performs pattern matching?

2 Answers