How can I find the correct prior status row in this table with a SQL query?

Imagine a workflow for data entry. Some forms come in, they are typed into a system, reviewed, and hopefully approved. However, they can be rejected by a manager and will have to be entered again.

So, an ideal workflow would go like this:

recieved > entered > approved

But this COULD happen:

received > entered > rejected > entered > rejected > approved

At each stage, we record who updated the form to its current status - who entered it, who rejected it, or who approved it. So the forms status table looks like this:

form_id status     updated_by updated_at
1       received   Bob        (timestamp)
1       entered    Bob        (timestamp)
1       approved   Susan      (timestamp)
2       received   Bob        (timestamp)
2       entered    Bob        (timestamp)
2       rejected   Susan      (timestamp)
2       entered    Carla      (timestamp)
2       rejected   Susan      (timestamp)
2       entered    Sam        (timestamp)
2       approved   Susan      (timestamp)

Here's what I'm开发者_运维问答 trying to do: write a rejection report. I want a row for each rejection, and joined to that row, I want to see who did the work that got rejected.

As a human, I can see that, for a given status row with status 'rejected', the row that will tell me who did the faulty work will be the one that

• shares the same form_id and
• has a prior timestamp closest to the rejection.

But I'm having trouble telling MySQL that.

Can anybody see how to construct this query?

A subselect ended up working for me.

SELECT 
  `s1`.`form_id`, 
  (
    SELECT 
      `s2`.`updated_by`
    FROM 
      statuses s2
    WHERE 
      `s2`.`form_id` = `s1`.`form_id`
      AND
        `s2`.`updated_at` < `s1`.`updated_at` 
    ORDER BY 
      `s2`.`updated_at` DESC
    LIMIT 1
  ) AS 'made_rejected_change'
FROM 
  statuses s1
WHERE
  `s1`.`status` = 'rejected'

Another solution that uses subselect (this time not a correlated subquery):

SELECT
  w1.*,
  w2.entered_by
FROM (
  SELECT
    wr.form_id,
    wr.updated_at AS rejected_at,
    wr.updated_by AS rejected_by,
    MAX(we.updated_at) AS entered at
  FROM workflow wr
    INNER JOIN workflow we ON we.status = 'entered'
      AND wr.form_id = we.form_id
      AND wr.updated_at > we.updated_at
  WHERE wr.status = 'rejected'
  GROUP BY
    wr.form_id,
    wr.updated_at,
    wr.updated_by
) w1
  INNER JOIN workflow w2 ON w1.form_id = w2.form_id
    AND w1.entered_at = w2.updated_at

The subselect lists all the rejecters and the immediately preceding entered timestamps. Then the table is joined once again to extract the names corresponding to the entered_at timestamps.

You want to get the rejected timestamp and then figure out the entry that appeared right before it based on the timestamp. I'm assuming that timestamp actually holds a date/time and isn't an SQL server timestamp field (completely different).

declare @rejectedTimestamp timestamp

select @rejectedTimestamp = timestamp
from table
where status = 'rejected'

select top 1 *
from table
where timestamp < @rejectedtimestamp
order by timestamp desc