Select records those are changed in compare to previous record on the basis of just one field

Question

Consider following sample data

| SN |        DateTime       | Status |
|----|-----------------------|--------|
|  1 | '2015-01-01 00:30:00' | OPEN   |
|  2 | '2015-01-01 00:35:00' | OPEN   |
|  3 | '2015-01-01 00:40:00' | CLOSED |
|  4 | '2015-01-01 00:50:00' | OPEN   |
|  5 | '2015-01-01 01:10:00' | OPEN   |
|  6 | '2015-01-01 01:15:00' | CLOSED |
|  7 | '2015-01-01 01:20:00' | CLOSED |
|  8 | '2015-01-01 01:30:00' | OPEN   |
|  9 | '2015-01-01 01:40:00' | OPEN   |
| 10 | '2015-01-01 01:52:00' | OPEN   |
| 11 | '2015-01-01 01:55:00' | CLOSED |
| 12 | '2015-01-01 02:15:00' | OPEN   |
| 13 | '2015-01-01 02:30:00' | OPEN   |

I need to select records where value for column 'Status' is changed from it's previous record. First record should always be returned.

I could do it using For loop in SQL Server but I want a better solution. Is it possible to do it in a single SELECT statement? The query should return rows with serial 1, 3, 4, 6, 8, 11 and 12.

Answer 1

You can use LAG to get previous Status value, then use this value in the WHERE clause of an outer query to get what you want:

SELECT SN, [DateTime], Status 
FROM (
   SELECT SN, [DateTime], Status, 
          LAG(Status) OVER (ORDER BY [DateTime]) AS prevStatus
   FROM mytable ) t
WHERE COALESCE(prevStatus, '') <> Status

Demo here