Wsrtjtyk

I ran the follow SQL Query and got the following rows as an output:

SELECT EventTime

      ,UserID

      ,SubAddr

FROM tablename

WHERE EventType = 20 AND 

    (SubAddr = 1 OR SubAddr = 2)

 EventTime  UserID  SubAddr 



2018-05-04 16:47:56.000 113 1    

2018-05-04 16:48:45.000 113 2    

2018-05-04 16:49:17.000 113 1    

2018-05-04 16:49:27.000 113 2    

2018-05-04 16:49:48.000 113 1    

2018-05-04 16:49:57.000 113 2    

2018-05-04 16:50:15.000 113 1    

2018-05-04 16:51:01.000 113 2    

2018-05-04 16:51:23.000 113 1    

2018-05-04 16:51:33.000 113 2    

2018-05-07 15:42:13.000 114 1    

2018-05-07 15:42:16.000 114 1    

2018-05-07 15:42:26.000 114 1    

2018-05-07 15:42:35.000 114 2    

2018-05-07 15:42:43.000 114 2    

2018-05-07 15:42:54.000 114 1    

2018-05-07 15:43:02.000 114 1    

2018-05-07 15:43:11.000 114 2    

2018-05-07 15:43:20.000 114 2    

2018-05-07 15:43:35.000 114 1    

2018-05-07 15:43:42.000 114 1    

2018-05-07 15:43:51.000 114 2    

2018-05-07 15:43:58.000 114 2

I want to change the query in such a way that it only gets the rows where it get the first eventtime for every user for both subaddr 1 and 2. If there is another row where subaddr is 1 after a row where is the same userID and subaddr = 2 I want it to retrieve that row too.

See the following example

 EventTime  UserID  SubAddr 



2018-05-07 15:42:13.000 114 1  <- This row    

2018-05-07 15:42:16.000 114 1  <- Not this row    

2018-05-07 15:42:26.000 114 1  <- Not this row    

2018-05-07 15:42:35.000 114 2  <- This row    

2018-05-07 15:42:43.000 114 2  <- Not this row    

2018-05-07 15:42:54.000 114 1  <- This row    

2018-05-07 15:43:02.000 114 1  <- Not this row    

2018-05-07 15:43:11.000 114 2  <- This row    

2018-05-07 15:43:20.000 114 2  <- Not this row    

2018-05-07 15:43:35.000 114 1  <- This row    

2018-05-07 15:43:42.000 114 1  <- Not this row    

2018-05-07 15:43:51.000 114 2  <- This row    

2018-05-07 15:43:58.000 114 2  <- Not this row

I hope this example make it clearer. I have been struggling with this all day and haven't been able to find an answer

Lee Mac solution don't working for me, because you will take in this table just

2018-05-07 15:42:13.000 114 1  <- This row    



2018-05-07 15:42:35.000 114 2  <- This row   

Because you selecting a min from grouping operator of user and subAddr.

I have simple solution (but you must check for you data integrity if working)
cut the data in this way:

2018-05-07 15:42

and Use this query

SELECT distinct(EventTime, UserID, SubAddr)

FROM tablename 

WHERE EventType = 20 AND (SubAddr = 1 OR SubAddr = 2)

in this way you will take this data:

2018-05-07 15:42:13.000 114 1  <- This row    



2018-05-07 15:42:35.000 114 2  <- This row    



2018-05-07 15:42:54.000 114 1  <- This row    

2018-05-07 15:43:02.000 114 1  <- Not this row (this will capture) 



2018-05-07 15:43:11.000 114 2  <- This row   



2018-05-07 15:43:35.000 114 1  <- This row    





2018-05-07 15:43:51.000 114 2  <- This row 

This is fast way, otherwise you need make a nested query, that will be more complex.

But i repeat, this is not solution to your problem, just a short way.

Use lag()

with t as (

      select t.*, lag(subaddr) over (partition by userid order by eventtime) as prev_subaddr

      from tablename t

      where EventType = 20 and SubAddr in (1, 2)

    )

select EventTime, UserID, SubAddr

from t

where prev_subaddr is null or prev_subaddr <> subaddr;

EDIT:

If you don't have lag(), you can use apply:

with t as (

      select t.*, tprev.subaddr as prev_subaddr

      from tablename t outer apply

           (select top (1) t2.*

            from tablename t2

            where t2.userid = t.userid and

                  t2.EventType = 20 and

                  t2.SubAddr in (1, 2) and

                  t2.eventtime < t.eventtime

            order by eventtime desc

           ) tprev

      where EventType = 20 and SubAddr in (1, 2)

    )

select EventTime, UserID, SubAddr

from t

where prev_subaddr is null or prev_subaddr <> subaddr;