Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :(

share|improve this question
    
This question is very vague. Can you provide more details along with CREATE table and some sample data and your expected results? That will provide you with accurate response. –  Kin Apr 10 '13 at 14:08
    
Please also add the queries you have tried already. –  Mark Storey-Smith Apr 10 '13 at 14:24
    
Oh, so everytime the flag changes, you want that record? –  thursdaysgeek Apr 10 '13 at 17:21
    
i doesn't save my tried queries. –  thomas Apr 11 '13 at 7:39
add comment

1 Answer

up vote 3 down vote accepted

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;
share|improve this answer
    
This looks very slick... does it work? –  RoKa Apr 10 '13 at 14:12
    
Works perfectly. BUT: this only SQL2012 compatible, right? –  RoKa Apr 10 '13 at 14:21
    
@RoKa It does in SQL Server 2012. Here is a SQL Fiddle: sqlfiddle.com/#!6/963ae/1/0 –  Brian Bentley Apr 10 '13 at 14:21
1  
@RoKa Yes, 2012 only. You would have to use a self join in previous versions. –  Brian Bentley Apr 10 '13 at 14:23
    
I have added a version using a self join that works with 2008. –  Brian Bentley Apr 10 '13 at 14:59
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.