This query doesn't take into account the time the job ran - if there is more than one run in a day we have not specified how we want this to be dealt with.

SQL Rank gives us a good idea of what we're looking at - also selecting step id = 0 gives the job outcome.

Select

[Job Name] = j.name

, [Job Description] = j.description

, [LastRunDate] = h.run_date

, [LastRunTime] = h.run_time

, [JobStatus] = Case h.run_status

When 0 Then 'Failed'

When 1 Then 'Successful'

When 3 Then 'Cancelled'

When 4 Then 'In Progress'

End

,[OrderOfRun] = Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc)

From

msdb.dbo.sysjobhistory h

Left join msdb.dbo.sysjobs j On j.job_id = h.job_id

Where h.step_id=0 --only look @ Job Outcome step

Order By [Job Name] desc,Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc) Asc