Get the most recently completed step for each SQL job I'm trying to get a list of jobs and their most recently completed step from the MSDB database. For a single job, this is pretty straight forward, something like:

 SELECT TOP 1 j.job_id, j.name, h.step_name, h.run_date, h.run_time, h.step_id
 FROM msdb.dbo.sysjobs j
 INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE j.name = 'my favorite job'
 ORDER BY h.run_date DESC, h.run_time DESC, h.step_id DESC


But how can I get a list of ALL jobs and their most recently completed step? 

Note this is SQL 2000, so I can't use PARTITION OVER. 
I also can't do a SELECT MAX() because its not a single RunDateTime field, I have to sort descending by two fields (OrderDay, OrderTime).

Edit: Probably not possible, but I'd love to be able to something like:

SELECT j.job_id, ...
FROM msdb.dbo.sysjobs j
INNER JOIN (SELECT TOP 1 step_name, run_date, run_time, step_id
            FROM msdb.dbo.sysjobhistory
            WHERE job_id = j.job_id
            ORDER BY run_date desc, run_time desc, step_id desc) as H


or something, but it doesn't seem to like syntax like that...
Unfortunately because there can be ties (the second-to-last step can take 0 seconds and therefore finish at "exactly" the same time as the last step), this is really not pretty in SQL Server 2000. Rather than calculate all of the datetime stuff multiple times I think the easiest way is to stuff it into a #temp table. You may consider creating a permanent view with the datetime conversion.

SELECT job_id, step_name, run_date, run_time, step_id, 
    run_datetime = CONVERT(DATETIME, RTRIM(run_date) + ' ' + 
    STUFF(STUFF(RIGHT('00000' + RTRIM(run_time), 6),3,0,':'),6,0,':'))
  INTO #t
      FROM msdb.dbo.sysjobhistory;

SELECT j.job_id, j.name, t.step_name, t.run_date, t.run_time,
       t.step_id, t.run_datetime
FROM #t AS t
INNER JOIN 
    (
      SELECT job_id, run_datetime = MAX(run_datetime)
      FROM #t GROUP BY job_id
    ) AS m 
    ON m.job_id = t.job_id
    AND m.run_datetime = t.run_datetime
INNER JOIN 
    (
      SELECT job_id, run_datetime, step_id = MAX(step_id) 
      FROM #t GROUP BY job_id, run_datetime 
    ) AS s 
    ON s.job_id = m.job_id
    AND s.run_datetime = m.run_datetime
    AND s.step_id = t.step_id
INNER JOIN 
    msdb.dbo.sysjobs AS j
    ON t.job_id = j.job_id
    AND m.job_id = j.job_id
    AND s.job_id = j.job_id;

DROP TABLE #t;