请教SQL中怎么GROUP BY 与TOP

请问SQL中如何GROUP BY 与TOP
一个表中的数据

ID nickname addtime
1 a 2011-12-19 11:11:12
2 b 2011-12-19 11:11:12
1 c 2011-12-19 11:11:10
2 c 2011-12-19 11:11:10
1 a 2011-12-19 11:11:11
2 b 2011-12-19 11:11:11

需求是根据时间倒序，取出每个ID，nickname 的前两条数据。

[解决办法]

SQL code

;WITH    tmp          AS ( SELECT   * ,                        rn = ROW_NUMBER() OVER ( PARTITION BY id ORDER BY nickname )               FROM     tb             )    SELECT  *    FROM    tmp    WHERE   rn <= 2
[解决办法]
SQL code
select * from (select no=row_number() over(partition by id order by addtime desc),* from tb)t where no<3
[解决办法]
SQL code
select   distinct b.*from  tb across apply  (select top 2 * from tb where id=a.id order by addtime desc)b