Home » SQL & PL/SQL » SQL & PL/SQL » Finding duplicate in table
Finding duplicate in table [message #399683] Thu, 23 April 2009 08:17 Go to next message
shahidmahmood2069
Messages: 11
Registered: February 2009
Junior Member
I have table bdata(no, name, add) with no primary key. How I can check is there any duplicate of (no, name) exist in the table because I want to have (no, name) as unique.
Re: Finding duplicate in table [message #399687 is a reply to message #399683] Thu, 23 April 2009 08:28 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
use COUNT and GROUP BY
Re: Finding duplicate in table [message #399926 is a reply to message #399683] Fri, 24 April 2009 08:59 Go to previous messageGo to next message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member

ops$rchamarthi> select * from dept;
 
   DEPT_ID D_NAME
---------- --------------------
        10 sales
        20 finance
        30 operations
        10 sales



-- To find duplicates.


  1  select * from dept
  2   where rowid not in
  3    (select max(rowid) from dept
  4*       group by dept_id,d_name)

ops$rchamarthi> /
 
   DEPT_ID D_NAME
---------- --------------------
        10 sales




Group by the columns which , when same, you consider them as duplicates.


Thanks,
Rajesh.





Re: Finding duplicate in table [message #399933 is a reply to message #399683] Fri, 24 April 2009 10:27 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Duplicates are one the most old and asked question please search BEFORE posting.

Regards
Michel
Previous Topic: data to be inactive
Next Topic: PLS-00382: expression is of wrong type
Goto Forum:
  


Current Time: Sat Dec 10 06:58:47 CST 2016

Total time taken to generate the page: 0.07574 seconds