ROW_NUMBER() OVER() 的用法（排序取第一行或者前几行）

1、只排序 ROW_NUMBER() OVER(ORDER BY OPERATEDATE desc) xh

select  ROW_NUMBER() OVER(ORDER BY OPERATEDATE desc) xh,* from PMS_TECHREPORT

只做排序，不分组，效果如下

2、分组加排序ROW_NUMBER() OVER(PARTITION BY sourceid ORDER BY OPERATEDATE desc) rn

 
 select  ROW_NUMBER() OVER(ORDER BY OPERATEDATE desc)as xh,a.* 
from ( 
	select (case when OPERATEDATE = '' then SUBMITDATE else OPERATEDATE end) 
	as OPERATEDATE
	 from PMS_TECHREPORT e  
	left join (
		select ROW_NUMBER() OVER(PARTITION BY sourceid ORDER BY OPERATEDATE desc) rn, r.SOURCEID,r.OPERATEDATE 
		from PMS_WORKFLOW_RECORD r 
		left join PMS_TECHREPORT t on r.SOURCEID = t.ID ) as re
	 on re.SOURCEID = e.ID and rn = 1
where minicurrentstateid='A010900' 
) a  

效果如下（rn的值不一样）