I have the following dataset:
| A | B | C |
|---|---|---|
| 1 | John | 2018-08-14 |
| 1 | John | 2018-08-20 |
| 1 | John | 2018-09-03 |
| 2 | John | 2018-11-13 |
| 2 | John | 2018-12-11 |
| 2 | John | 2018-12-12 |
| 1 | John | 2020-01-20 |
| 1 | John | 2020-01-21 |
| 3 | John | 2021-03-02 |
| 3 | John | 2021-03-03 |
| 1 | John | 2020-05-10 |
| 1 | John | 2020-05-12 |
And I would like to have the following result:
| A | B | C |
|---|---|---|
| 1 | John | 2018-08-14 |
| 2 | John | 2018-11-13 |
| 1 | John | 2020-01-20 |
| 3 | John | 2021-03-02 |
| 1 | John | 2020-05-10 |
If I group by A, B the 1st row and the third just concatenate which is coherent. How could I create another columns to still use a group by and have the result I want.
If you have another ideas than mine, please explain it !
I tried to use some first, last, rank, dense_rank without success.
CodePudding user response：
Use lag(). Looks like B is a function of A in your data. So checking lag(A) will suffice.
select A,B,C
from (
select *, case when lag(A) over(order by C) = A then 0 else 1 end startFlag
from mytable
) t
where startFlag = 1
order by C