Angel Ricardo Vega Hernandez - 9 months ago 21

SQL 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`

`Col2 = 'a'`

Answer

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
```