CODEWITHSUNDEEP
sql-serversql-server-2008
hero9989
hero9989

Reputation: 65

Find most recent record according to date in the table

Not sure how to put this without it being specific to my data - I want to find the most recent value for each person according to the date in the table

PayHistory Table

PayHistoryID, EmployeeID,  Date,        PayRate
1,            1,          '2017-01-01', 20000
2,            2,          '2017-01-01', 21000
3,            3,          '2017-01-01', 22000
4,            3,          '2017-05-01', 24000
5,            4,          '2017-01-01', 20000
6,            4,          '2017-06-01', 24000

Employee Table

EmployeeID, EmployeeName
1,          Bob
2,          Frank
3,          Jess 
4,          Alex

I want to bring back 

EmployeeID, EmployeeName
1,          20000
2,          21000
3,          24000 
4,          24000

Upvotes: 1

Views: 69

Answers (4)

Abdulkadir Erkmen
Abdulkadir Erkmen

Reputation: 202

You sohuld use ROW_NUMBER to list your rows and select only the first ones. Here a example of query for your tables, hope this helps;

Select EmployeeID,PayRate FROM (
Select ROW_NUMBER() OVER(Partition By e.EmployeeID Order By [DATE] DESC) AS RN,e.EmployeeID,PayRate FROM @employee e
INNER JOIN @payHistory p on p.EmployeeID = e.EmployeeID
) as P
Where RN = 1

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2506

Using FIRST_VALUE:

create table #PayHistory (PayHistoryID int, EmployeeID int, [Date] date, PayRate int)

insert into #PayHistory values(1, 1, '2017-01-01', 20000)
insert into #PayHistory values(2, 2, '2017-01-01', 21000)
insert into #PayHistory values(3, 3, '2017-01-01', 22000)
insert into #PayHistory values(4, 3, '2017-05-01', 24000)
insert into #PayHistory values(5, 4, '2017-01-01', 20000)
insert into #PayHistory values(6, 4, '2017-06-01', 24000)

select distinct EmployeeID, FIRST_VALUE(PayRate) OVER (Partition by EmployeeID order by [date] desc) from #PayHistory

The equivalent for versions earlier than 2012:

select distinct ph.EmployeeID
   , (Select top 1 Payrate from #PayHistory where EmployeeID = ph.EmployeeID ORDER BY [DATE] desc) as [PayRate]
from #PayHistory ph

Upvotes: 2

Mikado68
Mikado68

Reputation: 123

You should use OUTER APPLY:

SELECT *
FROM Employee E
OUTER APPLY (SELECT TOP 1 PayRate FROM PayHistory PH WHERE PH.EmployeeID = 
E.EmployeeID ORDER BY [date] DESC) T

Upvotes: 1

Raphael Müllner
Raphael Müllner

Reputation: 438

ROW_NUMBER() is the way to go:

select
    EmployeeID
    ,PayRate
from
(
    select
        rownum = row_number() over(partition by EmployeeID order by ph.[Date] desc)
        ,e.EmployeeID
        ,PayRate
    from EmployeeTable e
        inner join PayHistoryTable ph on e.EmployeeID = ph.EmployeeID
) x
where rownum = 1

Upvotes: 2

Related Questions