it is working fine........what is the problem.......please describe .......... select a.*,b.* from emp a, emp b where a.deptno =b.deptno and a.rowid<>b.rowid
this code is not working to avoid dup. records. when ever we use 1) a.deptno=b.deptno and 2)a.rowid<>b.rowid then oracle satisfies both conditions due to and operator. in emp table suppose take 10 as deptno(which is having 3 times in that table) according to this coding 1)condition satisfies ie both tables having same deptno 2)condition satisfies so that record will not be retrived. so it is satisfied for one record with deptno as 10. what about the remaining two records having deptno as 10. the 2)condition will not satisfies for these two records thats why these records retrived . (these r duplicate records)
This query will return the first row for each unique id in the table. This query could be used as part of a delete statement to remove duplicates if needed.
SELECT ID
FROM func t1
WHERE ROWID = (SELECT MIN (ROWID)
FROM func WHERE ID = t1.ID)
Also: You can use a group by without a summary function SELECT ID
FROM func t1
GROUP BY id
Mudit Sharma
Aug 8th, 2007
Hi, I think this is not the right query for getting unique values of a column from a table. Like a table EMP contains the column like DEPTNO having duplicate values.
For getting unique values of DEPTNO query will be like this:
< select a.DEPTNO from emp a, emp b where a.deptno=b.deptno and a.rowid not in (select max(rowid) from emp) group by a.deptno; >
SQL> select * from emp;ENAMEENOSALARY---------- ---------- ----------abc 118000def215000ghi 322000abc118000def215000ghi322000Using Group by function we can avoid the duplicate records instead of using distinct keyword.SQL> select ename,eno,salary from emp group by ename,eno,salary;ENAMEENOSALARY---------- ---------- ----------abc118000
def 2 15000
ghi 3 22000
SQL> select distinct * from emp;ENAME ENO SALARY ---------- ---------- ---------- abc 1 18000 ghi 3 22000 def 2 15000
Use analytical function as below to delete the duplicate rows. Delete FROM ROWID IN (SELECT ROWID FROM (SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_ID) RN FROM EMP) WHERE RN > 1)); This will delete the duplicate rows from the emp table on the basis of duplicates in employee name.
diva
Sep 27th, 2011
select dup_col from emp group by dup_col;
Tauseef
Oct 6th, 2011
SELECT FIRST_NAME
FROM EMPLOYEES E
WHERE ROWID = (SELECT MIN (ROWID)
FROM EMPLOYEES WHERE FIRST_NAME = E.FIRST_NAME)
It will check for each row values if it has duplicate then using rowid min of that duplicate will be avoided
Excellent.. It works like a charm. I was googling for more than one hour, and finally my search ended here. Would be great if there was a little explanation to add. Thanks again
AMAR
Feb 24th, 2015
Using Group by clause
Selvaraj
May 28th, 2015
SELECT *
FROM (SELECT N.*, COUNT(*)OVER(PARTITION BY N.EMP_NAME) RN
fROM EMPLOYEE N)
WHERE RN > 1; the inner query will return number of rows, if the row count >1 means it is duplicate record.
Rashid
Jun 21st, 2015
Here It is:
Code
SELECT*FROM tablename a
WHERE a.ROWID > ANY (SELECT b.ROWID
FROM tablename b
WHERE a.fieldname = b.fieldname
AND a.fieldname2 = b.fieldname2)
Malyadri M
Jun 22nd, 2015
Code
SELECTROWID, E.*
FROM EMP E
WHEREROWID=(SELECTMAX(ROWID)
FROM EMP1 E1
WHERE E.ENO = E1.ENO
);
Pritty
Feb 29th, 2016
The GROUP BY keyword helps to retrieve the unique values without using DISTINCT keyword.
How can we avoid duplicate rows. without using distinct command
Questions by subbaraovangalli answers by subbaraovangalli
Related Answered Questions
Related Open Questions