SQL查询以获取记录每种类型具有最大ID号的记录 [英] Sql Query to get the record what has the max id number for each type

问题描述

我在sql server中有一张表.它具有一个用作主键的pid字段，以及一个具有设备名称的名为deviceid的字段.例如:

I have a table in sql server. It has a pid field used as a primary key and a field called deviceid that has a device name. example:

pid  deviceid   field3 field4
1    Device1    test    test
2    Device2    test2   test2
3    Device1    test3   test3
4    Device2    test4   test4

对于查询，我需要从表中选择*，其中pid是每个设备的最大值. 结果示例:

For a query, i need select * from the table where the pid is the max per device. Example result:

pid  deviceid   field3  field4
3    Device1    test3   test3
4    Device2    test4   test4

我不确定该怎么做.有人有想法吗?

Im not sure how do do this. Any one have any ideas?

我最接近的是:

Select MAX(pid) from TheTable Group By deviceid;

这有效，但是它只给我每个设备在结果中的最大pid，我需要该记录的所有字段.在select子句中添加其他字段会导致错误，提示您需要在group by子句中列出这些字段...有人知道如何执行此操作吗?

This works but it only gives me the max pid per device in the results and i need all the field for that record. Adding in the other fields to the select clause resulted in errors saying that the fields need to be listed in the group by clause... Anyone know how to do this?

推荐答案

select *
from (
       select *,
              row_number() over(partition by deviceid order by pid desc) as rn
       from TheTable
     ) as T
where rn = 1

这篇关于SQL查询以获取记录每种类型具有最大ID号的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！