Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Finding Duplicates

Re: Finding Duplicates

From: Rob Abrahams <R.AbrahamsGeenSpam_at_HCCnet.nl>
Date: Sat, 12 Feb 2005 22:25:07 +0100
Message-ID: <420e7420$0$1346$3a628fcd@reader2.nntp.hccnet.nl>


SELECT x, y, z, MIN(somedate) FROM sometable GROUP BY x, y, z UNION ALL /* or use only UNION without ALL for delete duplicates between MIN and MAX */
SELECT x, y, z, MAX(somedate) FROM sometable GROUP BY x, y, z

"Rishi Pahuja" <rishipahuja_at_rediffmail.com> schreef in bericht news:1108237613.526713.11620_at_f14g2000cwb.googlegroups.com...
> Hello Everyone,
>
> If any one can help me to write a query to delete duplicates from a
> table using 'analytic functions' it will be helpful.
>
> All I want is to keep two records from a set of duplicates the most
> oldest and most recent one.
>
> Eg:
> 1. X Y Z 10/2/2004
> 2. X Y Z 10/3/2004
> 3. X Y Z 10/4/2004
>
> Output should be rows returned for 1 and last entry.
>
> I want to achieve this with one query and not a PL-SQL code. Also the
> table I fire query will be 100M + records.
>
> Thanks in advance.
>
> Rishi
>
Received on Sat Feb 12 2005 - 15:25:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US