Return to Revisions

A few things:

1. You are using ROW_NUMBER(), not RANK()
2. For the example you cite, it does not really make much difference
3. For other situations, ROW_NUMBER() provides additional flexibility. For example, suppose you are doing a data cleanse, and you want to read you input table and write all the duplicated rows into another table, so that you can process them further. Then you can do something like:

insert /*+ APPEND */ into table2
select * from (
  select col1
        , col2
        , col3
        , col4
   row_number() over (partition by <key columns> order by <xxx> ) rn
   from table )
where rn > 1