Friday, September 28, 2012

SQL MAX() RecordID ordered by date

I had a task where I needed to get the most recent timesheet record for task. A task can have many instances on the timesheet. I made use of the ROW_NUMBER() over SQL statement to first order the records by date. Then I got the record where the recordNumber was equal to 1.
select 
 tt.taskTimesheetID
from(
 select 
  ROW_NUMBER() over (partition by tt.taskID order by tt.startTime asc, tt.CreateDate desc ) as recordNumber
  ,tt.taskTimesheetID
  ,tt.taskID
 from tasktimesheet as tt
 where tt.isactive = 1
  and tt.taskID = 5254
   
 ) as data 
inner join taskTimesheet as tt on tt.taskTimesheetID = data.taskTimesheetID
 and data.recordNumber = 1

No comments:

Post a Comment