Geek asks and answers

Sql Query to get a record that has the maximum id number for each type

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

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

For the query, I need to select * from the table where pid is the maximum for each device. Example result:

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

I am not sure how to do this. Does anyone have any ideas?

The closest I got:

Select MAX(pid) from TheTable Group By deviceid;

This works, but it gives me the maximum number of pid for each device in the results, and I need the entire field for this entry. Adding select fields to other fields led to errors saying that the fields should be listed in the group by clause ... Does anyone know how to do this?

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

... , .

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)

Using select top 1 pidin the subquery, you can select any order proposal that you like to decide which 1 row to return for each device. 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)
+2
source

More articles:


All Articles