hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Mar 27th, 2007

 

Using Self join like :
  select dup.column from tab a,tab b where a.dup.column=b.dup.column and a.rowid<>b.rowid

Shiril Kumar Dubey

• Mar 27th, 2007

 

This code is not working perfectly.


select a.deptno
from scott.emp a
, scott.emp b
where a.deptno =b.deptno
and a.rowid<>b.rowid
/

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Mar 27th, 2007

 

Ok then try it....

DELETE FROM emp  WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id);

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Mar 27th, 2007

 

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

subbaraovangalli

 Profile Answers by subbaraovangalli Questions by subbaraovangalli

• Mar 28th, 2007

 

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)

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Apr 5th, 2007

 

suppose u have a table and have data like below:

table (func)
id

--

1

4

3

12

21

2

1

2

21

11

11
then try this u will get all duplicate data

select a.id from func a,func b

where a.id=b.id

and a.rowid<>b.rowid

MDP

• Apr 11th, 2007

 

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;
>

Regards
Mudit

bambino_chakma

 Profile Answers by bambino_chakma 

• Aug 12th, 2007

 

try this out!

select job from emp
where rowid in(select min(rowid)
from emp
group by job)


regards
Bambino chakma

sribala30

 Profile Answers by sribala30 

• Mar 21st, 2008

 

Group By can be used, to avoid duplicates.

Suppose if you have a table "T" with a column "C" having values 1,2,3,4,1,2,3,4.. .. ..

The following query returns unique values:

Select C From T Group By C;

ashwinee2

 Profile Answers by ashwinee2 

• Apr 10th, 2008

 

There is a very simple solution to this.

Suppose there is a table emp containing employee ids:
EmpID
1
1
2
2
2
3
3

Now you need to get the value 1,2 and 3

The query should be
SELECT EmpID from emp
group by EMPID

This will return you all distinct rows from the table

ITsforu

 Profile Answers by ITsforu 

• Apr 13th, 2008

 

You can use rank analytical function over all rows in your table and partition rows on row number.

select emp_id,ename from (  select emp_id,ename, rank() over (partition by emp_id,ename order by rowid  emp_rank from emp)  twhere emp_rank=1 order by emp_id,ename 

srinath1102

 Profile Answers by srinath1102 

• Apr 22nd, 2008

 

Hey guys, Try with UNION .

select 2 from dual
union
select 2 from dual

satyam_Ora

 Profile Answers by satyam_Ora Questions by satyam_Ora

• May 6th, 2008

 

SQL> select * from emp; ENAME             ENO     SALARY---------- ---------- ----------abc                  1      18000def            2      15000ghi              3      22000abc              1      18000def            2      15000ghi              3      22000 Using 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; ENAME             ENO     SALARY---------- ---------- ----------abc              1      18000

def              2      15000

ghi            3      22000

SQL> select distinct * from emp;ENAME             ENO     SALARY
---------- ---------- ----------
abc              1      18000
ghi              3      22000
def            2      15000

neha2701

 Profile Answers by neha2701 

• May 31st, 2008

 

how abt using -

select * from emp a

UNION

select * from emp b;

navin05_06

 Profile Answers by navin05_06 

• Sep 8th, 2008

 

Use Selfjoin in case of one table and equijoin in case of multiple tables

kondleravi

 Profile Answers by kondleravi 

• Sep 9th, 2008

 

select a.no  from (tablename) t1 a where rowid notin (select (max(rowid) from t1 b where a.no=b.no)

sandip01

 Profile Answers by sandip01 Questions by sandip01

• May 24th, 2010

 

select * from emp b
 where exists( select * from emp a
 where a.name=b.name and  
 a.rowid > b.rowid)

neelapu

 Profile Answers by neelapu 

• May 27th, 2010

 

if u want to see the duplicate values in a column u can use select stmt. 

if u wnat to delete the duplicate values in a column u can use delete stmt.

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

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

sandip.dhopat

 Profile Answers by sandip.dhopat Questions by sandip.dhopat

• Jun 5th, 2011

 

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

Katheeja Parveen

 Profile Answers by Katheeja Parveen 

• Feb 13th, 2015

 

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
SELECT ROWID, E.*


FROM EMP E


WHERE ROWID = ( SELECT MAX(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.