I have a sql query that groups by ID and TYPE that returns the following result set
ID DATE TYPE
101667712, 2008-10-31, W9
101667712, 2009-01-05, W9
101667712, 2009-01-05, W9
101667712, 2009-01-05, W9
101667712, 2009-01-05, W9
101667712, 2009-01-05, W9
101667712, 2009-01-05, W9
101667712, 2009-01-05, W8
101667712, 2009-01-08, W8
101667712, 2009-01-08, W9
101667712, 2009-01-08, W9
101667712, 2009-01-08, W9
101667712, 2009-01-09, W9
101667712, 2009-01-09, W9
101667712, 2009-10-28, W9
This result set has been ordered by ID and TYPE. The unordered result set shows that the sets with identical ID and TYPE are spread throughout the table. Apparently the group by clause has limited range, hence I get several groups with the same ID and TYPE spread throughout the result set. Ordering by ID, DATE, and TYPE puts them adjacent to each other, which is part of what I want.
What I want to do is to group all results by ID and TYPE until the TYPE changes so the results would look like this:
101667712, W9
101667712, W8
101667712, W9
(I've omitted the dates).
But if I use the original query as a subquery and the enclosing select statement groups by ID and TYPE what I get is:
101667712, W9
101667712, W8
The second group by combines all of the W9 sets into one. This eliminates the change from W9 -> W8 -> W9 and makes it appear that there was only a change from W9 -> W8. In this case group by must be looking ahead enough to combine all of the W9 groups.
Is there a way to make group by stop when one of the group by parameters changes?
A more general question is how far ahead the group by clause looks when constructing sets. I haven't seen any discussion of this in the SQL books I've checked.
Here's the query:
select id, date, type from type_audit where type in ('W8', 'W9') group by id, date, type
This is on Oracle 10g.