Home » SQL & PL/SQL » SQL & PL/SQL » selecting only duplicate records
selecting only duplicate records [message #287338] Wed, 12 December 2007 00:37 Go to next message
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 #287345 is a reply to message #287338] Wed, 12 December 2007 00:52 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Hi Sunil,

Provide a test case with create table and insert table scripts and your expected output.

Regards,
Kiran.
Re: selecting only duplicate records [message #287351 is a reply to message #287345] Wed, 12 December 2007 01:10 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Sunil,

The keywords you need to look into are: GROUP BY and HAVING

As Kiran pointed out: we can't offer you the solution for your specific request as we don't have your specific data.

So either you provide us with that information or you have a try and/or search with the above mentioned keywords.

Regards,
Marc
Re: selecting only duplicate records [message #287352 is a reply to message #287338] Wed, 12 December 2007 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to define how 2 rows are duplicates. If you have a primary key then rows can never be duplicated.
If you think about columns except primary key one, you can use COUNT function and GROUP BY/HAVING clauses, for instance.

Don't use IM speak, read and follow OraFAQ Forum Guide.

Regards
Michel
Re: selecting only duplicate records [message #287361 is a reply to message #287338] Wed, 12 December 2007 01:32 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

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 #287364 is a reply to message #287361] Wed, 12 December 2007 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For yuor last query, using COUNT in its analytical form will prevent you from acessing the table twice.

By the way, use COUNT(*) not COUNT(1), the result is the same but logically why do you want to count ones?

Regards
Michel

Re: selecting only duplicate records [message #287368 is a reply to message #287338] Wed, 12 December 2007 01:44 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

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 #287369 is a reply to message #287368] Wed, 12 December 2007 01:49 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The only truth in your last post is probably this part of the sentence: "I remember one of my senior told me ...". Search Ask Tom; there's a discussion about the issue.
Re: selecting only duplicate records [message #287370 is a reply to message #287368] Wed, 12 December 2007 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
count(*) and count(1) behave the same.
Neither read the rows (unless you have a condition on a column, in this case they both read up to this column).

Regards
Michel
Re: selecting only duplicate records [message #287371 is a reply to message #287368] Wed, 12 December 2007 01:52 Go to previous messageGo to next message
Maaher
Messages: 7062
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 #287372 is a reply to message #287368] Wed, 12 December 2007 01:54 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Michal,

very good debate on difference count(*) or count(1) between DBA = Developer.

Developer never accept count(*) or count(1) is equal.
Mr. Tom kyte explain it very well Smile
http://asktom.oracle.com/pls/asktom/f?p=100:11:2348117919541151::::P11_QUESTION_ID:1156159920245


PS: same thing happen with me with my developer.
Re: selecting only duplicate records [message #287373 is a reply to message #287371] Wed, 12 December 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I love this thread. Thanks Maarten.

In addition, here a couple of threads on Tom Kyte:
difference between count(1) and count(*)
Select Count(1): How it works
COUNT(*) Vs COUNT(1) on tables with CLOBS and etc

Regards
Michel

[Time to search and Mohammad post one before]

[Updated on: Wed, 12 December 2007 02:01]

Report message to a moderator

Re: selecting only duplicate records [message #287378 is a reply to message #287373] Wed, 12 December 2007 02:05 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
thanks kir_ait..
I got the soln...[Smile]..
hello Michel Cadot..
whole row was not duplicate...i was worrying abt column2 only.
Regards
Sunil Gaurav
Re: selecting only duplicate records [message #287381 is a reply to message #287338] Wed, 12 December 2007 02:11 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Oh yes. I got to know about this in deep.
And till now i had wrong assumption, today it cleared.
Thanks everyone.

Happy Regards,
Kiran.
Re: selecting only duplicate records [message #288172 is a reply to message #287381] Sat, 15 December 2007 06:39 Go to previous messageGo to next message
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.


commit;

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
Re: selecting only duplicate records [message #288174 is a reply to message #288172] Sat, 15 December 2007 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use the analytic form of count and max funtions.

Regards
Michel
Re: selecting only duplicate records [message #288179 is a reply to message #288174] Sat, 15 December 2007 07:58 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
I m not getting it Michel...plz can u help in detatail..?

Thanks and Regards
Sunil Gaurav
Re: selecting only duplicate records [message #288186 is a reply to message #288179] Sat, 15 December 2007 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
COUNT
MAX

See the examples.

Regards
Michel

[Updated on: Sat, 15 December 2007 09:59]

Report message to a moderator

Re: selecting only duplicate records [message #288197 is a reply to message #287338] Sat, 15 December 2007 15:02 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member

Frank, hobbes, MarcL, joy_division, Mahesh Rajendran, Barbara Boehmer, rleishman, rajavu1

Which of you are going to hand hold THIS newbie?
Re: selecting only duplicate records [message #289042 is a reply to message #288197] Wed, 19 December 2007 06:01 Go to previous message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
I got the solutions..
thanks alot to kir_ait,Michel Cadot and Maaher.

Regards
Sunil gaurav
Previous Topic: User and schema
Next Topic: DBMS_Scheduler
Goto Forum:
  


Current Time: Tue Dec 06 06:15:59 CST 2016

Total time taken to generate the page: 0.06063 seconds