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).