Methods for processing duplicate records

The measurement of duplicate is the values of two or more records for columns(prod_id,cust_id,time_id) are the same.

select prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold from (
   select prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold,
   rank() over(partition by   prod_id,cust_id,time_id order by rowid)p1
   from sales_copy1
  )v where v.p1=1;

select prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold from (
   select prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold,
   row_number() over(partition by   prod_id,cust_id,time_id order by rowid)p1
   from sales_copy1
  )v where v.p1=1;

The following sql sentence is to select duplicate records with max amount_sold:

select prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold from (
   select prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold,
   row_number() over(partition by   prod_id,cust_id,time_id order by amount_sold desc)p1
   from sales_copy1
  )v where v.p1=1;


The following sql sentence uses group by method.
   select prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold
   from sales_copy1
   where (rowid)in(select min(rowid) from sales_copy1 group by              prod_id,cust_id,time_id
  )

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24057587/viewspace-735392/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24057587/viewspace-735392/