SQL - how we can eliminate duplicates without using distinct command?

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 159 of 171

how we can eliminate duplicates without using distinct command?

Total Answers and Comments: 8

Last Update: October 29, 2009 Asked by: Suganthidinesh

Sponsored Links

Best Rated Answer

—

Submitted by: rjk1203

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 .

Above answer was rated as good by the following members:
singh13

June 10, 2008 03:20:19

siripala

Member Since: May 2008 Contribution: 1

RE: how we can eliminate duplicates without using distinct command?

select column_with_duplicates count(*) from table_name group by column_with_duplicates;

will give you distinct column values against number of ocurances

Is this answer useful? Yes | No

June 20, 2008 02:06:11

ashish88

Member Since: June 2008 Contribution: 8

RE: how we can eliminate duplicates without using distinct command?

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

Is this answer useful? Yes | No

July 13, 2008 05:27:17

suresh.kurapati11

Member Since: July 2008 Contribution: 2

RE: how we can eliminate duplicates without using distinct command?

Use below query
select unique * from table_name;

Is this answer useful? Yes | No

0 2

Overall Rating: -2

September 22, 2008 17:34:42

vlaroyia

Member Since: September 2008 Contribution: 1

RE: how we can eliminate duplicates without using distinct command?

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
)

Is this answer useful? Yes | No

September 25, 2008 09:07:18

rjk1203

Member Since: August 2008 Contribution: 5

RE: how we can eliminate duplicates without using distinct command?

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 .

Is this answer useful? Yes | No

1 0

Overall Rating: +1

December 05, 2008 08:35:55

kantri

Member Since: October 2008 Contribution: 4

RE: how we can eliminate duplicates without using distinct command?

SELECT UNIQUE deptno FROM emp;
It will give you distinct deptno.

Is this answer useful? Yes | No

October 26, 2009 06:26:04

ravishengg

Member Since: October 2009 Contribution: 1