Get Top 1 From Data For Every Employee

Question

I have a query which produces data like this

select c.Id, a.userId as Payroll, c.CurrentLocation, c.Longitude, c.Latitude, c.TextOtherLocation, c.CreationDate
    from PeopleDB.dbo.masterpeople a
    inner join PeopleDB.dbo.masterPeople b on a.manager = b.userid
    inner join PeopleTracking c on a.userId = c.payroll
    where a.Manager = '20090036'
ORDER BY Id DESC

Id  Payroll     CurrentLocation       Longitude     Latitude    TextOtherLocation   CreationDate
51  20180002    Field Work Location   107.5588565   -6.9077868                      6/13/2020 19:56
50  20180002    Field Work Location   107.5588565   -6.9077868                      6/14/2020 19:56
49  20190026    Office                107.5587918   -6.9077061                      6/15/2020 19:56
48  20190026    Field Work Location   107.5588565   -6.9077868                      6/16/2020 19:56
47  20190026    Office                107.5588565   -6.9077868                      6/17/2020 19:56

How can I get just any top 1 data descending for any payroll from above data, what I want is:

Id  Payroll     CurrentLocation       Longitude     Latitude    TextOtherLocation   CreationDate
51  20180002    Field Work Location   107.5588565   -6.9077868                      6/13/2020 19:56
49  20190026    Office                107.5587918   -6.9077061                      6/15/2020 19:56

Thanks for your help.

Answer 1

Use row_number():

select t.*
from (select c.Id, a.userId as Payroll, c.CurrentLocation, c.Longitude, c.Latitude, c.TextOtherLocation, c.CreationDate,
             row_number() over (partition by a.userid order by c.CreatedDate desc) as seqnum
      from PeopleDB.dbo.masterpeople a join
           PeopleDB.dbo.masterPeople b
           on a.manager = b.userid join
           PeopleTracking c
           on a.userId = c.payroll
      where a.Manager = '20090036'
     ) t
where seqnum = 1
ORDER BY Id DESC;

Note that a, b, and c are really bad choices for table aliases. These should be table abbreviations, respectively something like mp1, mp2 and pt.