selecting only duplicate records [message #287338] |
Wed, 12 December 2007 00:37  |
gauravsunil
Messages: 68 Registered: August 2007 Location: bhubaneswar
|
Member |
|
|
Hi Experts,
I have to select a column from table,this column has lots duplicate records along with unique records...all record is coming with a primary key
plz help me to filter only duplicate records so that my search become easy...
select column1,column2 from table1;
here column1 is primary key.
plz ask me in case of insufficient informetion..
Thanks and Regards
Sunil Gaurav
|
|
|
|
|
|
Re: selecting only duplicate records [message #287361 is a reply to message #287338] |
Wed, 12 December 2007 01:32   |
|
Again more specifically, if you want to check only the number of record in column2 has duplicate then you can use GROUP BY and HAVING clauses. Further, if you want only those duplicate records on col2 along with the primary key, then you can get it using ROWID. Check the following example and work on it.
SQL> create table table1(col1 number, col2 number);
Table created.
SQL> INSERT INTO TABLE1 ( COL1, COL2 ) VALUES (
2 7, 6);
1 row created.
SQL> INSERT INTO TABLE1 ( COL1, COL2 ) VALUES (
2 6, 5);
1 row created.
SQL> INSERT INTO TABLE1 ( COL1, COL2 ) VALUES (
2 5, 4);
1 row created.
SQL> INSERT INTO TABLE1 ( COL1, COL2 ) VALUES (
2 4, 4);
1 row created.
SQL> INSERT INTO TABLE1 ( COL1, COL2 ) VALUES (
2 3, 4);
1 row created.
SQL> INSERT INTO TABLE1 ( COL1, COL2 ) VALUES (
2 2, 3);
1 row created.
SQL> INSERT INTO TABLE1 ( COL1, COL2 ) VALUES (
2 1, 3);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from table1;
COL1 COL2
---------- ----------
7 6
6 5
5 4
4 4
3 4
2 3
1 3
7 rows selected.
SQL> SELECT col2, COUNT (1)
2 FROM table1
3 GROUP BY col2
4 HAVING COUNT (1) > 1;
COL2 COUNT(1)
---------- ----------
3 2
4 3
SQL> SELECT a.col1, a.col2
2 FROM table1 a
3 WHERE a.col2 IN (SELECT b.col2
4 FROM table1 b
5 WHERE b.col2 = a.col2 AND b.ROWID <> a.ROWID) ;
COL1 COL2
---------- ----------
5 4
4 4
3 4
2 3
1 3
SQL> drop table table1;
Table dropped.
Hope this is clear for you.
Kiran.
|
|
|
|
Re: selecting only duplicate records [message #287368 is a reply to message #287338] |
Wed, 12 December 2007 01:44   |
|
Michel,
Quote: | By the way, use COUNT(*) not COUNT(1), the result is the same but logically why do you want to count ones?
|
I don't know is this correct or not, i remember One of my senior told me don't use count(*) as it will take lot time when number of records huge, and also told me that * will search whole table and count(1) will look only at first column so that it will take less time.
How much this true?
Kiran.
[Updated on: Wed, 12 December 2007 01:44] Report message to a moderator
|
|
|
|
|
Re: selecting only duplicate records [message #287371 is a reply to message #287368] |
Wed, 12 December 2007 01:52   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
(c) Billy Verreynne@OTN forums | The CIP (Counter Idiot Protection) feature of Oracle will resolve this in future.
oracle@dev ~> sqlplus billy/***@dev
SQL*Plus: Release 12.0.0.3.0 - Production on Sun Aug 1 10:23:51 2010
Copyright (c) 1982, 2004, 2010 Oracle. All rights reserved.
Connected to:
Oracle Database 12g Enterprise Edition Release 12.0.0.3.0 - 128bit Production
With the Partitioning, Data Mining and Counter Idiot Protection options
SQL> select count(1) from scott.emp;
select count(1) from dba_tables
*
ERROR at line 1:
ORA-66601: Idiot SQL detected.
ORA-00028: your session has been killed
SQL>
| I immediately thought of this reply in this thread of the OTN forums. He has edited his post in mean time but the thread still is interesting. No offence intended of course.
MHE
[Updated on: Wed, 12 December 2007 02:19] Report message to a moderator
|
|
|
|
|
|
|
Re: selecting only duplicate records [message #288172 is a reply to message #287381] |
Sat, 15 December 2007 06:39   |
gauravsunil
Messages: 68 Registered: August 2007 Location: bhubaneswar
|
Member |
|
|
Im facing a new problem regarding it...
i want to select all rows which are duplicate in a group and find out all which are less than greates one...
i m rpoviding the test code ...
create table test(emp_no number(5) primary key,ename varchar2(20));
Table created.
insert into test123 values(1,'1');
insert into test123 values(2,'1');
insert into test123 values(3,'1');
insert into test123 values(4,'1');
insert into test123 values(5,'2');
insert into test123 values(6,'2');
insert into test123 values(7,'2');
insert into test123 values(8,'3');
insert into test123 values(9,'3');
insert into test123 values(10,'4');
insert into test123 values(11,'4');
insert into test123 values(12,'5');
insert into test123 values(13,'6');
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
select * from test123;
EMP_NO ENAME
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3
9 3
10 4
11 4
12 5
13 6
select count(EMP_NO),max(EMP_NO),ename from test123 having count(EMP_NO)>1 group by ename ;
COUNT(EMP_NO) MAX(EMP_NO) ENAME
4 4 1
3 7 2
2 9 3
2 11 4
I want output as all emp_no which has corresponding duplicate ename but they are less than max(emp_no)
i.e.
i m expecting here output as..
EMP_NO ENAME
1 1
2 1
3 1
5 2
6 2
8 3
10 4
Please help me to solve this prblm..
Thanks and Regards
Sunil Gaurav
|
|
|
|
|
|
|
|