Tracking currrent and previous status

Question

I have a table which keeps the status and created_timestamp corresponding to a process_id. One row is inserted as soon as the process status changes. So, there are as many rows with same process_id as there are statuses associated with it. I want to create another table/view using this data which has one single row corresponding to a process_id, its current status and its previous status. I need to make a Informatica job for this, but a SQL query will be just as helpful.
Sample Input:

Process_id | Status     | Created 
         1 | In_queue   | 2014-08-01 00:01:01 
         1 | Started    | 2014-08-01 01:03:01 
         1 | In_process | 2014-08-01 01:50:20 
         1 | Complete   | 2014-08-01 03:10:20 

Sample Output: 
Process_id | Previous_status | Current_status | Updated 
         1 | In_process      | Complete       | 2014-08-01 03:10:20

Answer 1

Performance aside, here's how I'd do it...

SELECT a.process_id
     , a.status
     , a.created
     , b.status prev_status
     , b.created prev_created
  FROM 
     ( SELECT x.*
            , COUNT(*) rank
         FROM my_table x
         JOIN my_table y
           ON y.process_id = x.process_id
          AND y.created >= x.created
        GROUP
           BY x.process_id
            , x.created
     ) a
  LEFT
  JOIN
     ( SELECT x.*
            , COUNT(*) rank
         FROM my_table x
         JOIN my_table y
           ON y.process_id = x.process_id
          AND y.created >= x.created
        GROUP
           BY x.process_id
            , x.created
     ) b
    ON b.process_id = a.process_id
 WHERE b.rank = a.rank + 1
   AND a.rank = 1;