### Stats

Friday, June 23, 2017
Not affiliated with Stack Overflow
Rip Tutorial: riptutorial@gmail.com

# Finding "out-of-sequence" records using the LAG() function

## Example

Given these sample data:

IDSTATUSSTATUS_TIMESTATUS_BY
1ONE2016-09-28-19.47.52.501398USER_1
3ONE2016-09-28-19.47.52.501511USER_2
1THREE2016-09-28-19.47.52.501517USER_3
3TWO2016-09-28-19.47.52.501521USER_2
3THREE2016-09-28-19.47.52.501524USER_4

Items identified by `ID` values must move from `STATUS` 'ONE' to 'TWO' to 'THREE' in sequence, without skipping statuses. The problem is to find users (`STATUS_BY`) values who violate the rule and move from 'ONE' immediately to 'THREE'.

The `LAG()` analytical function helps to solve the problem by returning for each row the value in the preceding row:

``````SELECT * FROM (
SELECT
t.*,
LAG(status) OVER (PARTITION BY id ORDER BY status_time) AS prev_status
FROM test t
) t1 WHERE status = 'THREE' AND prev_status != 'TWO'
``````

In case your database doesn't have LAG() you can use this to produce the same result:

``````SELECT A.id, A.status, B.status as prev_status, A.status_time, B.status_time as prev_status_time
FROM Data A, Data B
WHERE A.id = B.id
AND   B.status_time = (SELECT MAX(status_time) FROM Data where status_time < A.status_time and id = A.id)
AND   A.status = 'THREE' AND NOT B.status = 'TWO'
``````