Reputation: 437
My EmployeeData
table is shown below
Employee Jobnum city state Datetime Action
--------------------------------------------------------------------------
Tom 123 mon La 2014-04-14 U
Tom 123 mon La 2014-04-14 D
Tom 123 mon La 2014-04-13 D
Tom 124 tue ca 2014-04-14 U
Tom 124 tue ca 2014-04-14 I
Jerry 143 hr NY 2014-04-14 D
Jerry 143 hr NY 2014-04-14 I
Jerry 143 hr NY 2014-04-14 I
When I query that table, I need to get the last records for each employee.
The output should look like
Employee Jobnum city state Datetime Action
---------------------------------------------------------------------------
Tom 123 mon La 2014-04-14 D
Tom 124 tue ca 2014-04-14 I
Jerry 143 hr NY 2014-04-14 I
Upvotes: 1
Views: 166
Reputation: 77876
Try this once
select ed.*
from Employeedata ed
inner join
(
select jobnum,max(datetime) as maxdate
from Employeedata
group by jobnum
) X
on ed.DATETIME = X.maxdate
and ed.jobnum = X.jobnum
order by ed.jobnum;
Demo fiddle here http://sqlfiddle.com/#!3/aa528/12
Upvotes: 1
Reputation: 69524
SELECT Employee,Jobnum,city ,[state], [Datetime], [Action]
FROM (
select Employee,Jobnum,city ,[state], [Datetime], [Action]
,ROW_NUMBER() OVER (PARTITION BY Employee,Jobnum ORDER BY [Datetime] DESC) AS rn
from EmployeeData
) Sub
WHERE rn = 1
Note
Avoid using Sql Server Key words for your column names, if you do have any column names which are key words in sql server use square brackets []
around them.
Upvotes: 4