CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-server-2017
Ororigami
Ororigami

Reputation: 31

Get Top 1 From Data For Every Employee

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.

Upvotes: 1

Views: 161

Answers (2)

alroc
alroc

Reputation: 28194

I normally use the row_number() windowing function to assign ordinals to rows like this.

select Id,Payroll,CurrentLocation,Longitude,Latitude, TextOtherLocation, CreationDate
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.Id desc) as PayrollRowNum
    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') as PayrollData where PayrollRowNum = 1

This assigns original numbers to each of the rows, with the numbering resetting for each a.userId. Then retrieve only the "first" one based on descending c.Id within each a.userId "group".

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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.

Upvotes: 2

Related Questions