Create a group in sql

Question

I have a table with the fields ORDER and FLAG as below and I want to create the GRP column using analytic functions. Can someone adivse how I can do this in sql please?

ORDER	FLAG	GRP
1	Y	1
2	N	1
3	N	1
4	Y	2
5	N	2
6	Y	3
7	Y	4

Answer 1

Looks like you need to start new group at each 'Y'. Then do running count/sum of them:

select
  t.*
  , sum(case flag when 'Y' then 1 end) over(order by order_id asc) as grp
from your_table t

Note: never use columns like order, key, from etc as they are reserved keywords in SQL. That's why I renamed order to order_id.