title: rank query in sql server 2000
body: <p>I am trying to create a query that will bring the last STATUS of a JOB_ID, this would be simple in SQL Server 2008 using the rank over partition function, but on SQL Server 2000 this function is not available.
  I have managed to put the job history in order by Job_ID ascending and DATETIME descening in a temporary table (<em>#JOB_HISTORY</em> -note that the pk_ID is generated in the temporary table using: <code>IDENTITY(int, 1,1)</code>), but here is the part where I got stuck, I dont know how to rank the records in this table.</p>
  
  <pre><code>SELECT 
    h1.pk_ID,
    h1.Job_ID,
    h1.Status,
    h1.DATETIME
  FROM #JOB_HISTORY h1
  ORDER BY h1.pk_ID ASC;
  </code></pre>
  
  <p><strong>#JOB_HISTORY RESULT:</strong></p>
  
  <pre><code>pk_ID,  Job_ID, STATUS,         DATETIME
  
  1       1234    Succeded.       2015-03-30 12:10
  2       1234    Failed.         2015-03-30 01:00
  3       1234    Failed.         2015-03-28 01:00
  4       5678    Failed.         2015-04-02 04:00
  5       5678    Succeded.       2015-04-01 04:00
  6       5678    Succeded.       2015-03-31 04:00
  </code></pre>
  
  <p><strong>DESIRED OUTPUT:</strong></p>
  
  <pre><code>Job_ID    STATUS       DATETIME
  1234      Succeded.    2015-03-30 12:10
  5678      Failed.      2015-04-02 04:00
  </code></pre>