Answer / raj

DELETE FROM EMP E1
WHERE ROWID <(SELECT MAX(ROWID) FROM EMP E2 WHERE E1.ENO =
E2.ENO)

Answer / nirmalendu

delete from table_name where rowid not in(select min(rowid)
from table_name group by column_name);

** column_name which having duplicate record

Answer / manjla

CREATE TABLE User_Details
(
UserID int ,
FName varchar (50),
MName varchar (50),
LName varchar (50),
Email varchar (50)
)

insert into User_Details values(1,'X','Y','Z','X@X.com')
insert into User_Details values(1,'X','Y','Z','X@X.com')
insert into User_Details values(2,'P','Q','R','P@P.com')
insert into User_Details values(3,'M','N','O','M@M.com')
insert into User_Details values(3,'M','N','O','M@M.com')

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable
(
UserID int ,
FName varchar (50),
MName varchar (50),
LName varchar (50),
Email varchar (50)
)
go
INSERT INTO #TempTable SELECT DISTINCT * FROM User_Details
go
TRUNCATE TABLE User_Details
go
INSERT INTO User_Details SELECT * FROM #TempTable

SELECT * FROM User_Details

Answer / bcaramu

delete from employee
where (empid, empssn)
not in
( select min(empid), empssn
from employee group by empssn);

Answer / kirankumar.vangeti

delete from emp
where rowid not in (select max(rowid)
from emp
group by emp_number);

Answer / apurva

delete from <tablename> rowid not in (select max(rowid)
from <tablename> group by <col.name where there are
repeating records>);

Answer / sivadasan

Sorry for the previous answer....

We can do like this ,

1. First we have to transfer all data from original_table
table to a temporary table .

create table Temp_table as select * from original_table;

2. Delete all record from Original Table....

delete original_table;

3. Now we can write a query by using INSERT and UNION

insert into original_table (select * from temp_table
UNION select * from temp_table);

any issues let me know.....

Answer / santosh kumar

simple answer for deleting duplicate record from a table.....
table:---
create table t1 (id number(5),name varchar2(20));

then:----
insert into t1 values(10,'a');
insert into t1 values(10,'a');
insert into t1 values(20,'b');
insert into t1 values(20,'b');
---after insertion it'll like this----
id name
10 a
10 a
20 b
20 b

-------------------------------------------------------------
delete from t1
where rowid not in (select min(rowid) from t1 group by name);

Answer / manjula

DELETE FROM User_Details WHERE UserID=(
SELECT t1.UserID FROM
( SELECT UserID, count(*) AS Counts FROM User_Details GROUP
BY UserID HAVING count(*) > 1 )AS t1
)

Answer / balaji

delete from sampletable where ids in(select ids from
sampletable group by ids having count(ids)>1)

how to check the 3rd max salary from an employee table? One of the queries used is as follows: select sal from emp a where 3=(select count(distinct(sal)) from emp b where a.sal<=b.sal). Here in the sub query "select count(distinct(sal)) from emp b where a.sal<=b.sal" or "select count(distinct(sal)) from emp b where a.sal=b.sal" should reveal the same number of rows is in't it? Can any one here please explain me how is this query working perfectly. However, there is another query to get the 3rd highest of salaries of employees that logic I can understand. Pls find the query below. "select min(salary) from emp where salary in(select distinct top 3 salary from emp order by salary desc)" Please explain me how "select sal from emp a where 3=(select count(distinct(sal)) from emp b where a.sal<=b.sal)" works source:http://www.allinterview.com/showanswers/33264.html. Thanks in advance Regards, Karthik.

4 Answers  

What is pl sql variable?

0 Answers  

What are the advantages of stored procedure?

0 Answers  

what is the difference between where clause and having clause? : Sql dba

0 Answers  

how do you restrict number of rows for a particular value in a column.For example:there is a table called fruits,having apples,bananas ,papayas.I dont want to have more than 100 apples in that table ,so how can u restrict number of rows for apple to hundred?

6 Answers  

Do stored procedures prevent sql injection?

0 Answers  

Explain ttitle and btitle.

0 Answers  

Why use triggers in sql?

0 Answers  

How to create a view on a table which does not exists

4 Answers   Oracle, TCS,

What pl/sql package consists of?

0 Answers  

What does subquery mean in sql?

0 Answers  

What is normalisation and its types?

0 Answers