SQL - How you will avoid duplicating records in a query?

GeekInterview.com

I am new, Sign me up!

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 9 of 171

Print

How you will avoid duplicating records in a query?

By using DISTINCT

Total Answers and Comments: 10

Last Update: July 13, 2009

Sponsored Links

Best Rated Answer

—

Submitted by: sumanpani

BY USING DISTINCT

LIKE

WE HAVE COLUMN DEPTNO
10
10
20
20
10

WHEN YOU USE
SELECT DISTINCT DEpTNO FROM EMP;

DEPTNO
------
    10
    20
    30

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

September 27, 2005 22:22:24

#1

savan

How you will avoid duplicating records in a query?...

a) delete from &&table_name t1 where t1.&&column_name in (select &&column_name
from &&table_name t2 where t1.rowid > t2.rowid and t1.&&column_name t2.&&column_name)
/
undefine table_name
undefine column_name
The script will prompt for a table name and a column name from which duplicate values are to be removed. It will then remove all rows with ROWID values higher than the lowest ROWID for this particular column.
b) Let us take a table containing 3 columns then we can use the following command to delete the duplicate rows from the table.
delete from table where rowid in ( SELECT rowid FROM group by rowid col1 col2 col3
minus SELECT min(rowid) FROM group by col1 col2 col3);
c) delete from table where rowid not in ( SELECT min(rowid) FROM group by col1 col2 col3 );
d) delete from employee where empid not in (Select min(empid) from employee)
e) select column_name from table_name group by column_name having count(*) > 1

Is this answer useful? Yes | No

October 17, 2005 02:33:26

#2

Member Since: October 2005 Contribution: 3

RE: How you will avoid duplicating records in a query?...

the best way is to use primary key;second thought if u dont have any constraints go for distinct..but my view is we can used distinct to select rather then to aviod...plz coment

Is this answer useful? Yes | No

October 17, 2005 02:34:10

#3

Member Since: October 2005 Contribution: 3

RE: How you will avoid duplicating records in a query?...

the best way is to use primary key;second thought if u dont have any constraints go for distinct..but my view is we can used distinct to select rather then to aviod...plz coment

Is this answer useful? Yes | No

January 29, 2007 02:36:21

#4

Rohan Deshpande

RE: How you will avoid duplicating records in a query?...

you can avoid duplicate rows by using DISTINCT.select distinct column_name from table_name;

Is this answer useful? Yes | No

March 09, 2007 06:08:20

#5

Nikhil_4_Oracle

RE: How you will avoid duplicating records in a query?...

HI ALL

The best way is primary key. since we all know that only one primary key per table.

so use combination of unique key and NOT NULL constraint(if req) its also as same

as Primary key.

Regards

Nikhil.

Is this answer useful? Yes | No

April 09, 2007 07:44:57

#6

shikha

RE: How you will avoid duplicating records in a query?...

By using Distinct we avoid dupication in records in a queryselect distinct column_name from table_name

Is this answer useful? Yes | No

August 07, 2007 05:30:09

#7

Member Since: August 2007 Contribution: 7

RE: How you will avoid duplicating records in a query?...

To avoid duplications in while entering data in tables we use primary key.
Distinct is the concept which is used in retriving the data.
for example if there is a table and there is no primary key for eno column and there are some employees with same eno.using distinct command we can retrive the data.

Is this answer useful? Yes | No

January 29, 2008 08:25:46

#8

patilpravin_1981

Member Since: December 2007 Contribution: 29

RE: How you will avoid duplicating records in a query?

using distinct keyword
another is union operator

Is this answer useful? Yes | No

September 16, 2008 01:31:09

#9

Member Since: September 2008 Contribution: 11

RE: How you will avoid duplicating records in a query?

BY USING DISTINCT

LIKE

WE HAVE COLUMN DEPTNO
10
10
20
20
10

WHEN YOU USE
SELECT DISTINCT DEpTNO FROM EMP;

DEPTNO
------
10
20
30

Is this answer useful? Yes | No

1 0

Overall Rating: +1

July 12, 2009 09:29:59

#10

Member Since: July 2009 Contribution: 4

RE: How you will avoid duplicating records in a query?

SELECT cons_no COUNT(cons_no) FROM master
HAVING COUNT(cons_no)>1
GROUP BY cons_no;

Is this answer useful? Yes | No

Sponsored Links

Sponsored Links

About Us - Privacy Policy - Terms and Conditions - Contact - Ask Question - Propose Category - Site Updates

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved