1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

in tabase table having a column in it empname field is
there which having 5 duplicate values is there i want
deleted all the duplicates i want showing only one name
only.

Question Posted / guest

Answers were Sorted based on User's Feedback



in tabase table having a column in it empname field is there which having 5 duplicate values is th..

Answer / karna

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

Is This Answer Correct ?    2 Yes 0 No

in tabase table having a column in it empname field is there which having 5 duplicate values is th..

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

Is This Answer Correct ?    1 Yes 0 No

in tabase table having a column in it empname field is there which having 5 duplicate values is th..

Answer / ambarish

we can use cursor.

Is This Answer Correct ?    0 Yes 0 No

in tabase table having a column in it empname field is there which having 5 duplicate values is th..

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.

Is This Answer Correct ?    0 Yes 0 No

in tabase table having a column in it empname field is there which having 5 duplicate values is th..

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

Is This Answer Correct ?    1 Yes 1 No

in tabase table having a column in it empname field is there which having 5 duplicate values is th..

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)

Is This Answer Correct ?    1 Yes 1 No

in tabase table having a column in it empname field is there which having 5 duplicate values is th..

Answer / dinesh gupta

use distinct commond

distinct(ename) from table name

Is This Answer Correct ?    0 Yes 2 No

Post New Answer

More SQL Server Interview Questions

What is the difference between coalesce() & isnull()?

0 Answers  

How to list all user defined functions in the current database?

0 Answers  

How to recover from sql injection? : sql server security

0 Answers  

Is it true that rules do not apply to data already existing in a database at the time the rule is created?

0 Answers  

What is the order in which the sql query is executed?

0 Answers  




Define magic tables in sql server?

0 Answers  

Which table keeps the locking information?

0 Answers  

What is sql server profiler?

0 Answers  

How to connect PK and FK?

2 Answers  

Explain triggers in sql?

0 Answers  

What is @@rowcount in sql?

0 Answers  

What is the difference between Userdefined function and stored procedure? Explain and give the example also

5 Answers  

For more SQL Server Interview Questions Click Here
Categories