Home » SQL & PL/SQL » SQL & PL/SQL » get duplicate record in a table
get duplicate record in a table [message #216706] Tue, 30 January 2007 01:27 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Hii all

suppose,i have a table that contain these records
       1
       2
       3
       4
       4
       5
       6
       7
       7
       8
       9
      10
      11
      11
      19



you see that ther are duplicate in the table (4,7,11)

how can i get that duplicate??

Thanks for everyone helped and helped me

Re: get duplicate record in a table [message #216708 is a reply to message #216706] Tue, 30 January 2007 01:34 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
you are here such a long time and you still haven't figured out the search facility of this board? That's sad.

One way to do that is to GROUP the records and display only those groups that are HAVING a COUNT greater than 1.

MHE
Re: get duplicate record in a table [message #216730 is a reply to message #216708] Tue, 30 January 2007 03:33 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi

Try this one


SQL > SELECT * FROM emp e where empno in
(select empno from emp d where d.ENAME=e.ENAME minus
select empno from emp f
where f.ENAME=e.ENAME
and rownum=1)


Rgds

Hari Krishna
Re: get duplicate record in a table [message #216732 is a reply to message #216730] Tue, 30 January 2007 03:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
hari.choprala wrote on Tue, 30 January 2007 10:33
Hi

Try this one
[snip]
Hari Krishna


Could you please show us the output you got when you tested this?
Re: get duplicate record in a table [message #216737 is a reply to message #216732] Tue, 30 January 2007 03:58 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi Frank,

Iam attaching txt file . Please Go Thru that.
if it is wrong then tell me the solution.

thanks

Rgds
Hari Krishna

  • Attachment: sample1.txt
    (Size: 2.00KB, Downloaded 139 times)
Re: get duplicate record in a table [message #216743 is a reply to message #216737] Tue, 30 January 2007 04:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
@Hari
Your solution will work for the duplicate records, which are not considering the key columns (like empno).
Seems OP is looking for the key column.

By,
Vamsi
Re: get duplicate record in a table [message #216749 is a reply to message #216743] Tue, 30 January 2007 04:29 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi Vamsi,

Thanks for Your suggestion.

Please check this one ,

SQL> select * from sample;

NUM
----------
1
2
3
4
4
5
6
7
7
8
9
10
11
11
19

15 rows selected


SQL> SELECT NUM FROM SAMPLE
2 GROUP BY NUM HAVING COUNT(NUM)>1
3 ;

NUM
----------
4
7
11

SQL>


Rgds
Hari Krishna
Re: get duplicate record in a table [message #216751 is a reply to message #216749] Tue, 30 January 2007 04:30 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Well well, how about that? Very Happy

MHE
Previous Topic: concatenating msg and statement
Next Topic: ConvertToClob does not work
Goto Forum:
  


Current Time: Fri Dec 09 00:29:19 CST 2016

Total time taken to generate the page: 0.18704 seconds