PL/SQL - how can we avoid duplicate rows. without using distinct command

GeekInterview.com > Interview Questions > Oracle > PL/SQL

Go To First | Previous Question | Next Question

PL/SQL | Question 172 of 241

how can we avoid duplicate rows. without using distinct command

Total Answers and Comments: 17

Last Update: September 09, 2008 Asked by: subbaraovangalli

Sponsored Links

Best Rated Answer

—

Submitted by: sribala30

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;

Above answer was rated as good by the following members:
manish.baehal

Sorting Options

Page 1 of 2

« First

Last »

March 27, 2007 05:31:41

hindol_saha

Member Since: March 2007 Contribution: 15

RE: how can we avoid duplicate rows. without using dis...

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

Is this answer useful? Yes | No

March 27, 2007 06:23:45

Shiril Kumar Dubey

RE: how can we avoid duplicate rows. without using dis...

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
/

Is this answer useful? Yes | No

March 27, 2007 07:34:48

hindol_saha

Member Since: March 2007 Contribution: 15

RE: how can we avoid duplicate rows. without using dis...

Ok then try it....

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

Is this answer useful? Yes | No

March 27, 2007 07:36:59

hindol_saha

Member Since: March 2007 Contribution: 15

RE: how can we avoid duplicate rows. without using dis...

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

Is this answer useful? Yes | No

March 28, 2007 07:58:14

subbaraovangalli

Member Since: March 2007 Contribution: 17

RE: how can we avoid duplicate rows. without using dis...

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)

Is this answer useful? Yes | No

April 05, 2007 07:25:43

hindol_saha

Member Since: March 2007 Contribution: 15

RE: how can we avoid duplicate rows. without using dis...

suppose u have a table and have data like below:

table (func)
id

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

Is this answer useful? Yes | No

April 11, 2007 16:21:09

MDP

RE: how can we avoid duplicate rows. without using dis...

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

Is this answer useful? Yes | No

August 08, 2007 01:51:46

Mudit Sharma

RE: how can we avoid duplicate rows. without using dis...

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

Is this answer useful? Yes | No

August 12, 2007 14:34:49

bambino_chakma

Member Since: July 2007 Contribution: 7

RE: how can we avoid duplicate rows. without using dis...

try this out!

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

regards
Bambino chakma

Is this answer useful? Yes | No

March 21, 2008 02:02:48

#10

sribala30

Member Since: November 2007 Contribution: 1

RE: how can we avoid duplicate rows. without using distinct command

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;

Is this answer useful? Yes | No

1 0

Overall Rating: +1

Page 1 of 2

« First

Last »

Go To Top

User Name		Remember Me?
Password

Question Bank