Re: Finding Duplicates

"Rishi Pahuja" <rishipahuja_at_rediffmail.com> a écrit dans le message de 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
|

To get the most recent and oldest one per col1, col2, col3:

select col1,col2,col3,coldate from
(select col1,col2,col3,coldate,
rank() over(partition by col1,col2,col3 order by coldate) r1, rank() over(partition by col1,col2,col3 order by coldate desc) r2 from t)
where r1=1 or r2=1
/

Regards
Michel Cadot Received on Sun Feb 13 2005 - 01:21:37 CST