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
