savan

• Sep 27th, 2005

 

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

vishal

 Profile Answers by vishal 

• Oct 17th, 2005

 

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

vishal

 Profile Answers by vishal 

• Oct 17th, 2005

 

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

Rohan Deshpande

• Jan 29th, 2007

 

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

Nikhil_4_Oracle

• Mar 9th, 2007

 

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.
 

shikha

• Apr 9th, 2007

 

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

velsowmya

 Profile Answers by velsowmya 

• Aug 7th, 2007

 

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.

patilpravin_1981

 Profile Answers by patilpravin_1981 

• Jan 29th, 2008

 

using distinct keyword
another is union operator

sumanpani

 Profile Answers by sumanpani 

• Sep 16th, 2008

 

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

rajiv_datasoft

 Profile Answers by rajiv_datasoft 

• Jul 12th, 2009

 

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

narmadac

 Profile Answers by narmadac 

• Feb 11th, 2010

 

Duplicating records can be avoided in two ways

First -- best method is assigning a Primary Key on the table.

Second --  If primary key is not assigned

select <column_names> from <table_name> where rowid in (select max(rowid) from <table_name> group by <column_name>);

ex: select ename, sal from emp where rowid in (select max(rowid) from emp group by eno);

ankitagujrati28

 Profile Answers by ankitagujrati28 

• Apr 30th, 2010

 

Primary key is the best way for that. But in case of not primary key we can use distinct keyword.

yagneswarasankar

 Profile Answers by yagneswarasankar 

• Sep 10th, 2010

 

SQL> select * from duplicate;

        ID     SALARY DATES
---------- ---------- --------------------
         1       3000 07-SEP-2010 14:28:22
         1       3000 07-SEP-2010 14:28:22
         2       3000 07-SEP-2010 14:28:22
         2       3000 07-SEP-2010 14:28:22
         3       3000 07-SEP-2010 14:31:43
         3       3000 07-SEP-2010 14:31:43
         3       3000 07-SEP-2010 14:32:18

7 rows selected.

SQL> delete from (select id,salary,dates from duplicate where rowid in (select max(rowid) from dupli
cate group by id,salary,dates));

4 rows deleted.

SQL> select * from duplicate;

        ID     SALARY DATES
---------- ---------- --------------------
         1       3000 07-SEP-2010 14:28:22
         2       3000 07-SEP-2010 14:28:22
         3       3000 07-SEP-2010 14:31:43

sivakp

 Profile Answers by sivakp 

• Mar 28th, 2011

 

select distinct deptno from emp group by deptno having deptno>1;

kethinenisarath

 Profile Answers by kethinenisarath 

• Mar 29th, 2011

 

HI


select * from emp
where sal in (select  sal  from emp
                      group by sal
                       having count(1)>1);



Thanks &Regards,
kethinenisarath
                    

kethinenisarath

 Profile Answers by kethinenisarath 

• Apr 9th, 2011

 

Hi! 
I think its best option to avoid duplicating records in a query

select * from emp

where sal in ( select sal from emp 

                    group by sal 

                   having  count(1)>1);

Thanks,

kethinenisarath