I need help in writing a query in Oracle for the following data. The data is sorted by Person and Day fields.
Person Day Flag
------ --- ----
person1 day1 Y
person1 day2 Y
person1 day3 Y
person1 day4 N
person1 day5 N
person1 day6 Y
person1 day7 Y
person1 day8 Y
I need to have a Group_Number column that gets incremented whenever the Flag value changes. My result should look as below
Person Day Flag Group_Number
------ --- ---- ------------
person1 day1 Y 1
person1 day2 Y 1
person1 day3 Y 1
person1 day4 N 2
person1 day5 N 2
person1 day6 Y 3
person1 day7 Y 3
person1 day8 Y 3
I think there is way to get above result using analytic functions such as ROW_NUMBER, LEAD etc.