Remove duplicates from query, while repeating

Question

I have an SQL table with some data like this, it is sorted by date:

+----------+------+
|   Date   | Col2 |
+----------+------+
| 12:00:01 | a    |
| 12:00:02 | a    |
| 12:00:03 | b    |
| 12:00:04 | b    |
| 12:00:05 | c    |
| 12:00:06 | c    |
| 12:00:07 | a    |
| 12:00:08 | a    |
+----------+------+

So, I want my select result to be the following:

+----------+------+
|   Date   | Col2 |
+----------+------+
| 12:00:01 | a    |
| 12:00:03 | b    |
| 12:00:05 | c    |
| 12:00:07 | a    |
+----------+------+

I have used the distinct clause but it removes the last two rows with Col2 = 'a'

Answer 1

You can use lag (SQL Server 2012+) to get the value in the previous row and then compare it with the current row value. If they are equal assign them to one group (1 here) and a different group (0 here) otherwise. Finally select the required rows.

select dt,col2 
from (
select dt,col2,
case when lag(col2,1,0) over(order by dt) = col2 then 1 else 0 end as somecol
from t) x 
where somecol=0

Answer 2

If you are using Microsoft SQL Server 2012 or later, you can do this:

select  date, col2
from    (
            select  date, col2,
                    case when isnull(lag(col2) over (order by date, col2), '') = col2 then 1 else 0 end as ignore
            from    (yourtable)
        ) x
where   ignore = 0

This should work as long as col2 cannot contain nulls and if the empty string ('') is not a valid value for col2. The query will need some work if either assumption is not valid.

Answer 3

same as accepted answer (+1) just moving the conditions
assumes col2 is not null

select dt, col2 
from ( select dt, col2 
       lag(col2, 1) over(order by dt) as lagCol2 
       from t
     ) x 
where x.lagCol2 is null or x.lagCol2 <> x.col2