Get history record(s) from log table

louispy

Suppose I have a table jobs with following schema and example records. This is a table of people given jobs at certain timestamps.

+----+--------+-----------+-----+------+
| id | Person | Timestamp | Job | Desc |
+----+--------+-----------+-----+------+
| 1  | A      | 1         | W   | w    |
+----+--------+-----------+-----+------+
| 2  | A      | 2         | X   | x    |
+----+--------+-----------+-----+------+
| 3  | B      | 2         | Y   | y    |
+----+--------+-----------+-----+------+
| 4  | A      | 3         | Z   | z1   |
+----+--------+-----------+-----+------+
| 5  | B      | 3         | Z   | z2   |
+----+--------+-----------+-----+------+
| 6  | A      | 4         | Y   | y    |
+----+--------+-----------+-----+------+
| 7  | A      | 5         | Z   | z3   |
+----+--------+-----------+-----+------+

Suppose that before getting the job Z, a person would get at least one other job which is not Z.

I need to select every record with job Z and the (one) previous record before getting the job Z.

Expected result from the example table.

+--------+-----------+-----+------+----------+---------------+
| Person | Timestamp | Job | Desc | Prev_job | Prev_job_desc |
+--------+-----------+-----+------+----------+---------------+
| A      | 3         | Z   | z1   | X        | x             |
+--------+-----------+-----+------+----------+---------------+
| B      | 3         | Z   | z2   | Y        | y             |
+--------+-----------+-----+------+----------+---------------+
| A      | 5         | Z   | z3   | Y        | y             |
+--------+-----------+-----+------+----------+---------------+

I was thinking to select from jobs where Job = Z. And then from the query result, loop for each person to previous record with timestamp < each timestamp from the query result. But then I don't think that it's the optimal solution.

Please let me know if anyone here have better solution(s).