SELECT col1,col2 ... coln
FROM sometable
WHERE (ROWID)
NOT IN
(
SELECT MAX(ROWID)
FROM sometable
GROUP BY col1,col2 ... coln
);

-----Original Message-----
From: Prem J Khanna [mailto:[Email Address Removed] Thursday, February 28, 2002 10:59 AM
To: LazyDBA.com Discussion
Subject: Re: Find duplicate rows


Mike ,

This works fine in case of just one duplicate record. [ hope i'm right !!!!
]
If there is more than one duplicate recoed... ????

i.e.,i may have many duplicate records.
but how do i select only one of the repeating records ?

Jp.

Mike Ault wrote:

> Here is an example of what I mean:
>
> delete from dept_copy
> where rowid in
> (select rowid from dept_copy
> minus
> select max(rowid) from dept_copy
> group by DEPTNO, DNAME, LOC);
>
> Mike
>
> -----Original Message-----
> From: [Email Address Removed] [mailto:[Email Address Removed] Sent: Thursday, February 28, 2002 10:22 AM
> To: LazyDBA.com Discussion
> Subject: Re: Find duplicate rows
>
> One more time, Assume fld1,fld2,fld3 constitute a unique record
>
> SELECT fld1,fld2,fld3
> FROM <table>
> GROUP BY fld1,fld2,fld3
> HAVING COUNT(*) > 1;
>
> Other ways but this will work
>
> Rick
>
> "wei"
> <[Email Address Removed] To: "LazyDBA.com Discussion"
> <[Email Address Removed] mail.com> cc:
> Subject: Find duplicate rows
> 02/28/2002
> 01:20 PM
>
> Hi all,
>
> I am sure this question has been asked many times but I don't seems to
find
> it from archive, here goes again. How do find all the duplicate rows in a
> table?
> thanks,
>
> WZ
>
> --------
> Oracle documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to oracledba-[Email Address Removed] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>
> --------
> Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to oracledba-[Email Address Removed] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
terms:http://www.lazydba.com/legal.html


--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page