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

SQL – Period range in subgroups of a group by

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.

Answer

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

Advertisement

Answer