select column_with_duplicates, count(*) from table_name group by column_with_duplicates;
will give you distinct column values against number of ocurances
Login to rate this answer.
select * from tale_name a where rowid >any(select rowid from table_name b where a.col1=b.col1)
this query show duplicate record ina table and use delete command to eleminate duplicate record in the table.
hope this helpful to you
Ashish Sharma
Login to rate this answer.
Use below query
select unique * from table_name;
Login to rate this answer.
One,
delete
from xyz a
where rowid >=
(select min(rowid)
from xyz b
where a.e_id = b.e_id)
Two,
delete from
xyz
where rowid in
(select rowid from
(SELECT
ROWID,
ROW_NUMBER()
OVER
(PARTITION BY E_ID ORDER BY E_ID) dup
from xyz)
where dup > 1);
Three,
If there are NULLS,
DELETE FROM
table_name A
WHERE
A.rowid >
ANY (SELECT B.rowid
FROM
table_name B
WHERE
A.col1 = B.col1
AND
A.col2 = B.col2
)
Login to rate this answer.
Assume 'location' is the column in 'Emp' table has several duplicates
EMP
Emp_Id varchar(10)
Location varchar(12)
you can remove the duplicates using 'union'
select location from emp union select location from emp ;
this will strip the duplicates .
Hope this will help .
3 Users have rated as useful.
Login to rate this answer.
SELECT UNIQUE deptno FROM emp;
It will give you distinct deptno.
Login to rate this answer.
Table Name: Tbl
Columns:A B C
DELETE FROM Tbl where rowid not in (select A,B,C,Max(rowid) from Tbl Group by A,B,C)
Login to rate this answer.
By using rowid we can elminate the duplicate records in a table
DELETE FROM TABLE_NAME WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM TABLE_NAME GROUP BY COLUMN_NAME);
Login to rate this answer.
delete from tablename where rowin not in (select max(rowid) from tablename group by dup_col_name)
Login to rate this answer.
Not only DISTINCT command and we can also use UNIQUE keyword.
Login to rate this answer.
Method 1:
SELECT col1, col2, col3 ….. --(list all the columns for which you want to eliminate duplicates)
FROM (SELECT col1, col2, col3,….. --(list all the columns as above), COUNT(*)
FROM table)
Method 2:
SELECT col1, col2, col3 ….. --(list all the columns for which you want to eliminate duplicates)
FROM table
UNION
SELECT col1, col2, col3 ….. --(list all the columns for which you want to eliminate duplicates)
FROM table (use the same table).
This union will remove the duplicates.
Login to rate this answer.
SELECT * from emp
wheresal in (select sal from emp
Group by sal
having count(1)>1)
Thanks
ketarathhinenis
Login to rate this answer.
By using JOIN command
Login to rate this answer.
Nikhil I
Answered On : Oct 12th, 2011
Suppose assume your table has 3 columns eno,ename,sal.
Code
DELETE FROM emp WHERE rowid IN(SELECT eno,ename,sal,max(rowid) FROM emp GROUP BY eno,ename,sal);
The above query will eliminates the duplicate records from the table.
Code
DELETE FROM emp WHERE rowid IN(SELECT eno,ename,sal,max(rowid) FROM emp GROUP BY eno,ename,sal);
Login to rate this answer.
