Aggregation Problem

Hi all,

I'm looking for a function that can calculate the following: I have a table like this:

STATE_ID | SERVICE_STATE

1        | OK
2        | OK
3        | ERROR
4        | ERROR
5        | ERROR
6        | ERROR
7        | ERROR
8        | OK
9        | OK

I want a query thats shows all rows of the table but has a new column. Which has a kind of group_id that is not changing if the SERVICE_SATE is not changing from one STATE_ID to the next per SEVICE_ID: STATE_ID | SERVICE_STATE | CONTINUE_GROUP

1        | OK            | 1
2        | OK            | 1
3        | ERROR         | 2
4        | ERROR         | 2
5        | ERROR         | 2
6        | ERROR         | 2
7        | ERROR         | 2
8        | OK            | 3
9        | OK            | 3

So in this example the SERVICE_STATE is changing for STATE_ID 2 to 3 from OK to ERROR so there is a new CONTINUE_GROUP = 2. And again a change from ERROR to OK for STATE_ID 7 to 8 and the new CONTINUE_GROUP = 3. Is there an aggregation function that can do this?

Thanks Björn Received on Tue Oct 23 2007 - 07:08:30 CDT