Sql Query to get the record what has the max id nu

问题:

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

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?

The closest i got was:

Select MAX(pid) from TheTable Group By deviceid;

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?

回答1:

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

回答2:

You were very close... just needed an additional join to get the rest of the results.

SELECT t.*
FROM
   TheTable t JOIN
   (
      Select MAX(pid) AS MAXpid 
      from TheTable 
      Group By deviceid
   ) maxt on maxt.MAXpid = t.pid

回答3:

select * from TheTable t where pid=(select top 1 pid from TheTable
  where deviceid=t.deviceid order by pid desc)

By using select top 1 pid in the subselect, you can pick any order clause you like, to decide which 1 row to return for each deviceid. A similar syntax works well in a join:

select r.deviceid, r.otherinfo, t.* from RelatedTable r
join TheTable t on t.pid=(select top 1 pid from TheTable
   where deviceid=r.deviceid order by pid desc)