Skip to content

Instantly share code, notes, and snippets.

@nurettin

nurettin/art.sql

Created May 9, 2018
Embed
What would you like to do?
group rows by pilot row, show line numbers and line count
select
i
, c
, g
, gi
, sum(1) over (partition by g) gn
from (
select
i
, c
, g
, row_number() over(partition by g order by i) gi
from (
select
i
, c
, sum(case c when 'a' then 1 else 0 end) over (order by i) g
from (
select 1 i, 'a' c union all
select 2, 'b' union all
select 3, 'c' union all
select 4, 'a' union all
select 5, 'b' union all
select 6, 'a' union all
select 7, 'b' union all
select 8, 'c' union all
select 9, 'd'
) t
) t
) t
order by i
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment