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 languages bi uses to achieve the goal?

0 Answers  

What is database replication? What are the different types of replication you can set up in sql server?

0 Answers  

How to create database with physical files specified in ms sql server?

0 Answers  

How to execute a sql statement using odbc_exec()?

0 Answers  

Mention the differences between substr and charindex in sql server.

0 Answers  




let's assume you have data that resides on sql server 6.5. You have to move it sql server 7.0. How are you going to do it? : Sql server database administration

0 Answers  

How does index makes search faster?

0 Answers   QuestPond,

What is the use of floor function in sql server?

0 Answers  

How to drop an existing table?

0 Answers  

How to convert numeric expression data types by assignment operations?

0 Answers  

Explain DBCC?

0 Answers   QuestPond,

If any stored procedure is encrypted, then can we see its definition in activity monitor?

0 Answers  

For more SQL Server Interview Questions Click Here
Categories