这个GROUP BY语句该怎么写？

[jrmd]

<img src="http://photoimg62.qq.com/cgi-bin/load_pic?verify=XIoa9C5kAWfCTCtAGW4ZTQ%3D%3D">
这是我的表，我想要选出这样的记录：

DeviceSN UseSN UseDeptID UseEmpID
1 3 99 99001
2 1 18 07001

要满足两个条件：1、DeviceSN不同，2、UseSN最大


SELECT a.DeviceSN, b.UseSN, b.UseDeptID
FROM (SELECT DeviceSN, max(UseSN) AS _UseSN FROM EQ_DeviceUseList GROUP BY DeviceSN) AS a
LEFT JOIN EQ_DeviceUseList AS b ON a.DeviceSN=b.DeviceSN
这个查询得到了如下的结果：

<img src="http://photoimg61.qq.com/cgi-bin/load_pic?verify=3veibxkAgz1n26gbSwwryQ%3D%3D">
但是我不想要第2条和第3条记录，该如何把它们排除掉？

[bzszp]

SELECT DeviceSN, UseSN, UseDeptID
from EQ_DeviceUseList
where (DeviceSN, UseSN) in(select DeviceSN,max(UseSN) from EQ_DeviceUseList b
group by b.DeviceSN);

[ycbao]

select * from
(select deviceSN,userSn,usedptid,useempid,row_number() over(partition by deviceSN
order by userSN desc) rn)
where rn=1;

[ycbao]

select * from
(select deviceSN,userSn,usedptid,useempid,row_number() over(partition by deviceSN
order by userSN desc) rn from tab)
where rn=1;

[bugchen888]

SELECT a.DeviceSN, b.UseSN, b.UseDeptID
FROM (
SELECT DeviceSN, max(UseSN) AS _UseSN
FROM EQ_DeviceUseList
GROUP BY DeviceSN) AS a
LEFT JOIN EQ_DeviceUseList AS b
ON a.DeviceSN=b.DeviceSN
AND a.UseSN=b._UseSN

[xjqqxjqq]

同意楼上的！