In relational problems that require grouping together specific rows to perform aggregates, it is sometimes the nature of the data that groups with similar data can only be distinguished by their ordering among all the rows in the set. For example, consider a sensor sampling at various times whether a device is running, outputting to this simple table: CREATE TABLE dbo.DeviceState (SampleTime datetime NOT NULL, Running bit NOT NULL) . We wish to collapse all rows where the state did not change into single rows in the form `MinSampleTime, MaxSampleTime, Running` with the state for each row in ascending time order alternating between Running and Not Running. Coming up with a query to perform this task is difficult. The RANK() function immediately comes to mind as a possible way to distinguish the Running groups from the Not Running groups, but has a fatal flaw: The value changes as soon as the time changes. What we need is a way to cause RANK() to order the rows by the SampleTime before the Status time, but then not use the changes in the SampleTime column to calculate the actual series values--depending only on the Running column to trigger an increment.