OVER(PARTITION BY INQUIRY_ID, SID, PPID, TYPE ORDER BY EMAIL_ID DESC)

1)table AA strucure:

inquiry_id , email_id ,  sid  ,  ppid  ,type


2)

select inquiry_id,sid,ppid,type 
from AA
group by inquiry_id,sid,ppid,type having count(1) =2
order by inquiry_id,sid,ppid


inquiry_id        sid              ppid      type count

8000013444326 6008824985041 1008186222 S    2
8000013444326 6008824985041 1008276447 S    2
8000013444378 6008802481609 1005683880 S    2
8000013444400 6008824780848 1003518478 S    2
8000013444400 6008824780848 1003518819 S    2


上面的结果是select 出来的，也就是每行记录有在AA中有两条纪录。但是有不一样的email_id

3）to do :

现在要把上面的每两条记录的email_id 大的那条纪录的type值变成"P"。

解答：

SELECT INQUIRY_ID, SID, PPID, DECODE(RN, 1, 'P', TYPE) TYPESS, COUNTS
  FROM (SELECT AA.*,
               ROW_NUMBER() OVER(PARTITION BY INQUIRY_ID, SID, PPID, TYPE ORDER BY EMAIL_ID DESC) RN,
               COUNT(1) OVER(PARTITION BY INQUIRY_ID, SID, PPID, TYPE) COUNTS
          FROM AA) A
 WHERE COUNTS > 1;



  
update INQUIRE_ALL_DETAIL_CURRENT
set email_type ='P' where email_id in
(
select distinct(email_id)
FROM (SELECT INQUIRE_ALL_DETAIL_CURRENT.*,ROW_NUMBER() OVER(PARTITION BY INQUIRY_ID, supplier_id,product_id, email_type ORDER BY EMAIL_ID DESC) RN,
COUNT(1) OVER(PARTITION BY INQUIRY_ID, supplier_id,product_id, email_type) COUNTS
  FROM INQUIRE_ALL_DETAIL_CURRENT) A
  WHERE COUNTS > 1 and rn=1
  )