SQL Server Return Rows Where Field Changed

Question

I have a table with 3 values.

ID     AuditDateTime       UpdateType
12     12-15-2015 18:09    1
45     12-04-2015 17:41    0
75     12-21-2015 04:26    0
12     12-17-2015 07:43    0
35     12-01-2015 05:36    1
45     12-15-2015 04:35    0

I'm trying to return only records where the UpdateType has changed from AuditDateTime based on the IDs. So in this example, ID 12 changes from the 12-15 entry to the 12-17 entry. I would want that record returned. There will be multiple instances of ID 12, and I need all records returned where an ID's UpdateType has changed from its previous entry. I tried adding a row_number but it didn't insert sequentially because the records are not in the table in order. I've done a ton of searching with no luck. Any help would be greatly appreciated.

Answer 1

By using a CTE it is possible to find the previous record based upon the order of the AuditDateTime

WITH CTEData AS 
(SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AuditDateTime) [ROWNUM], *
FROM @tmpTable)
SELECT A.ID, A.AuditDateTime, A.UpdateType 
FROM CTEData A INNER JOIN CTEData B 
        ON  (A.ROWNUM - 1) = B.ROWNUM AND 
            A.ID = B.ID 
WHERE A.UpdateType <> B.UpdateType 

The Inner Join back onto the CTE will give in one query both the current record (Table Alias A) and previous row (Table Alias B).