sql去除多余记录的有关问题

sql去除多余记录的问题
现在有一个用户登录表UserLogin，一个用户ID有多次的登录时间，例如：
userid logintime
101 '2012-08-14 10:00'
101 '2012-08-14 10:20'
101 '2012-08-14 10:30'
101 '2012-08-14 11:00'
102 '2012-08-15 11:30'
102 '2012-08-14 10:15'
...

如何最多只保存每个用户的最新的两条登录信息，得到如下的结果：
userid logintime
101 '2012-08-14 10:00'
101 '2012-08-14 10:20'
101 '2012-08-14 10:30'
102 '2012-08-15 11:30'
102 '2012-08-14 10:15'
...
求赐教，不胜感激！

[解决办法]

SQL code

--是要删除表中多余的数据，保留每个用户最新的两条的数据吗？如：delete UserLogin from UserLogin awhere (select count(distinct logintime) from userlogin where userid=a.userid and logintime>=a.logintime)>2--如果是查询最新的2条，不改变原始数据select * from UserLogin awhere (select count(distinct logintime) from userlogin where userid=a.userid and logintime>=a.logintime)<=2
[解决办法]
对userid分组，使用row_number()over()排序 取<3就行。
[解决办法]
建立插入触发器在触发器中引用一楼的删除语句：
 delete UserLogin from UserLogin a
where a.userid=(select userid from inserted) and logintime not in (select top 2 logintime from userlogin where userid=(select userid from inserted) order by logintime)
[解决办法]
此问题有点类似：http://www.dbfaq.net/FAQ/NewQL.aspx?QuestionID=71请参考
[解决办法]
表记录的重复数据删除：
http://www.dbfaq.net/FAQ/FixupQL.aspx?QuestionID=30

[解决办法]

; with cte ( select row_number() over( paritition by userid order by userid, logintime desc ) as xh ,* from UserLogin )  

delete from cte where xh >=3
[解决办法]
探讨

; with cte ( select row_number() over( paritition by userid order by userid, logintime desc ) as xh ,* from UserLogin ) 

delete from cte where xh >=3