Inside Microsoft® SQL Server® 2008: T-SQL Querying

Grouping Factor

In earlier chapters, Chapter 6 in particular, I described a concept called a grouping factor. I used it in a problem to isolate islands, or ranges of consecutive elements in a sequence. Recall that the grouping factor is the factor you end up using in your GROUP BY clause to identify the group. In the earlier problem, I demonstrated two techniques to calculate the grouping factor. One method was calculating the maximum value within the group (specifically, the smallest value that is both greater than or equal to the current value and followed by a gap). The other method used row numbers.

Because this chapter covers aggregates, it is appropriate to revisit this very practical problem. In my examples here, I’ll use the Stocks table, ...