In March 2014, I wrote about two methods for ad hoc grouping: “Grouping Sequences” and “Start of Group“. I just found out that Rob van Wijk wrote on the same subjects two months earlier. We even used the same example!
I mention Rob’s article for two reasons:
- If my explanation wasn’t clear enough, you can try Rob’s.
- When I use “Start of Group”, Rob uses a similar method he calls “max-on-case-row-number”. These techniques are completely equivalent. Execution plans and performance are identical.
The “Start of Group” method can take many forms. Don’t think you need to know every one! Just choose the form you understand and like best, and use it all the time.
Don’t forget, when you get 12c you can replace both methods by the
MATCH_RECOGNIZE clause :)