Interested to Buy Any Domain ? << Click Here >> for more details...
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.
- 7 Answers
- 10066 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
Answer / dinesh gupta
use distinct commond
distinct(ename) from table name
| Is This Answer Correct ? | 0 Yes | 2 No |
what is the maximum size of a row? : Sql server database administration
If you lose rights to your sql server instance what are the options to connect to sql server instance? : sql server security
What is the maximum size of sql server database?
What does it mean to normalize data?
What are triggers? How do you invoke a trigger on demand?
Is mysql the same as sql server?
hi, may i know what is the command to get abstract the current month, current year and current day from a given date.i want these three in a isolated way..seperatedly is that any way in sql server 2000
What is entity data services?
What do you understand by replication in sql server?
What is Cross Join
What is a database in ms sql server?
How should i optimize the time for execution of stored procedure having single input and many output from the different tables?
-
Oracle (3259) - SQL Server (4518)
- MS Access (429)
- MySQL (1402)
- Postgre (483)
- Sybase (267)
- DB Architecture (141)
- DB Administration (291)
- DB Development (113)
- SQL PLSQL (3330)
- MongoDB (502)
- IBM Informix (50)
- Neo4j (82)
- InfluxDB (0)
- Apache CouchDB (44)
- Firebird (5)
- Database Management (1411)
- Databases AllOther (288)
