Sql-server – how to show only changed rows

sql servert-sql

if want querying the following table :

EventDateTime (DateTime), Host (guid), Flag ( bit)
2013-04-01 05:00, {id-x}, 0
2013-04-01 05:01, {id-x}, 0
2013-04-01 05:02, {id-x}, 0
2013-04-01 05:03, {id-x}, 0
2013-04-01 05:04, {id-x}, 1
2013-04-01 05:05, {id-x}, 1
2013-04-01 05:06, {id-x}, 1
2013-04-01 05:07, {id-x}, 0
2013-04-01 05:08, {id-x}, 0
2013-04-01 05:09, {id-x}, 0
2013-04-01 05:10, {id-x}, 0
2013-04-01 05:11, {id-x}, 1
2013-04-01 05:12, {id-x}, 1
2013-04-01 05:13, {id-x}, 1
2013-04-01 05:14, {id-x}, 1
2013-04-01 05:15, {id-x}, 0

and the result should be:

2013-04-01 05:04, {id-x}, 1
2013-04-01 05:07, {id-x}, 0
2013-04-01 05:11, {id-x}, 1
2013-04-01 05:15, {id-x}, 0

i have try it with LAG,LEAD and OVER Clause. but no luck 🙁

Best Answer

Here is a version for SQL Server 2012 using the new LAG feature:

WITH CTE AS
(
    SELECT
        EventDateTime,
        Host,
        Flag,
        LAG(Flag)
            OVER (ORDER BY EventDateTime) AS PrevFlag
    FROM MyTable
)
SELECT EventDateTime, Host, Flag
FROM CTE
WHERE Flag <> PrevFlag;

Here is a version for SQL Server 2008 using a self join:

WITH CTE AS 
(
    SELECT
        EventDateTime,
        Host,
        Flag,
        ROW_NUMBER()
            OVER (ORDER BY EventDateTime) AS RowNum
    FROM MyTable
)
SELECT
    C.EventDateTime,
    C.Host,
    C.Flag
FROM CTE AS C
    INNER JOIN CTE AS D
    ON C.RowNum = D.RowNum +1
WHERE C.Flag <> D.Flag;
Related Question