redsam redsam - 6 months ago 8
SQL Question

Get most recent job's data for all unique jobs

I am having a hard time figuring out where the problem is in my code. I Have a few jobs running periodically at different frequencies. From those jobs I need data for most recent job running data for all unique jobs from Jobs Table.

Any help will be highly appreciated.

Jobs - Master Table

Job_Id Name
1 Job1
2 Job2
3 Job3


Jobs history

Id Job_Id StartTime EndTime
100 1 2015-03-11 16:03:49.760 2015-03-11 16:09:49.760
101 2 2015-03-10 16:03:49.760 2015-03-10 16:40:49.760
102 3 2015-02-11 16:03:49.760 2015-02-11 16:09:49.760
103 2 2015-03-11 16:03:49.760 2015-03-11 16:09:49.760
104 1 2015-03-11 16:08:49.760 2015-03-11 16:12:49.760
105 1 2015-03-11 16:12:49.760 2015-03-11 16:16:49.760
106 1 2015-03-11 16:20:49.760 2015-03-11 16:29:49.760


Output: I Need something like below. Most recent Job data for each job. Each job frequency is not the same.

Id Name StartTime EndTime
106 Job1 2015-03-11 16:20:49.760 2015-03-11 16:29:49.760
103 Job2 2015-03-11 16:03:49.760 2015-03-11 16:09:49.760
102 Job3 2015-02-11 16:03:49.760 2015-02-11 16:09:49.760





SELECT top 1* FROM Jobs j
INNER JOIN JobsHistory jh ON j.Id = jh.Job_Id
where jh.Job_Id in (select Id from Jobs )
order by jh.starttime desc

Answer

Try something like this

The ROW_NUMBER() with the OVER() clause will number you jobs, sorted by the StartTime (descending) and do this numbering partitioned for each Job_Id. All rows with the number 1 should be the most recent per Job_Id

SELECT *
FROM
(
    SELECT ROW_NUMBER() OVER(PARTITION BY Job_Id ORDER BY StartTime DESC) AS OrderNr
          ,*
    FROM  Jobs  j
      INNER JOIN  JobsHistory jh ON j.Id = jh.Job_Id
) AS tbl
WHERE tbl.OrderNr=1