在SQLServer下使用row_number()对其select结果再次分组

下图为使用select 选择后的数据，想对其按照Lotid进行分组，并取最大的cleantimes的一笔数据。

sql语句：

SELECT w.WORKORDER,p.NAME,p.WAFERSIZE, w.LastProcessEqp ,lh.LOTID,w.CleanTimes,lh.QTY
    FROM lothistory LH,allwafer w,product p,tool t,workorder wo
	WHERE lh.TOOLID=t.id AND lh.LOTID=w.LOTID AND wo.workorderid=w.workorder AND wo.productname=p.name
				AND LH.LOTDATETIME >= '2013-10-14 00:00:00.000' AND LH.LOTDATETIME <= '2013-10-15 00:00:00.000' AND w.CleanTimes >=3 and LH.QTY>0 and t.Name like 'fc%' and LH.ACTION='MoveOut'
				     GROUP BY w.WORKORDER,p.NAME,p.WAFERSIZE, w.LastProcessEqp,lh.LOTID,w.CleanTimes,lh.QTY

结果：

使用row_number()基本用法：

select * 
from
(
select *,
       row_number() over(partition by LOTID order by CleanTime desc) as rownum
from tb
) t 
where rownum=1

最终代码代码：

select WORKORDER,NAME,WAFERSIZE, LastProcessEqp ,LOTID,CleanTimes,QTY
from
(
select *,
       row_number() over(partition by LOTID order by CleanTimes desc) as rownum
from (SELECT w.WORKORDER,p.NAME,p.WAFERSIZE, w.LastProcessEqp ,lh.LOTID,w.CleanTimes,lh.QTY
    FROM lothistory LH,allwafer w,product p,tool t,workorder wo
	WHERE lh.TOOLID=t.id AND lh.LOTID=w.LOTID AND wo.workorderid=w.workorder AND wo.productname=p.name
				AND LH.LOTDATETIME >= '2013-10-14 00:00:00.000' AND LH.LOTDATETIME <= '2013-10-15 00:00:00.000' AND w.CleanTimes >=3 and LH.QTY>0 and t.Name like 'fc%' and LH.ACTION='MoveOut'
				     GROUP BY w.WORKORDER,p.NAME,p.WAFERSIZE, w.LastProcessEqp,lh.LOTID,w.CleanTimes,lh.QTY
	  )bbb
) as tg
where rownum=1