Search code examples
sqlpostgresqlgroup-byanalytics

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.

Solution

  • 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