partition by的用法

有这样一张表A

fk_eventid                  fk_assetid           sum

90010                         4                              80

90006                         1                              20

90005                         1                              15

90008                         3                              50

90008                         2                              40

90009                         4                              100

90007                         4                               70

90011                         3                               30

 

 

现在我要得出这样的一个需求：得出每个fd_assetid的TOP2（按SUM排),然后再按SUM降序排列

比如通过上面的数据可以得出下面的结果

select    fk_eventid,fk_assetid,sum   from 

(

      select fk_eventid,fk_assetid,sum,row_number() over(partition by fk_assetid order by sum desc) recno from A) where recno<=2  order by sum desc

 

)

 

fk_eventid                 fk_assetid            sum

-------------                 -------------           ------------- 

90009                          4                            100

90010                          4                             80

90008                          3                             50

90008                          2                             40

90011                          3                              30

90006                          1                              20

90005                          1                              15

 

关于row_number() over的用途可以见文章＜关于row_numbert()  over与rank()   over＞