Monday, 9 September 2013

SQL - Group rows via criteria until exception is found

SQL - Group rows via criteria until exception is found

I am trying to add a Group column to a data set based on some criteria.
For a simple example:
----------
| ID | Data |
----------
| 1 | 12 |
----------
| 2 | 20 |
----------
| 3 | 03 |
----------
| 4 | 55 |
----------
| 5 | 11 |
Let's say our criteria is that the Data should be greater than 10. Then
the result should be similar to:
----------
| ID | Data | Group |
----------
| 1 | 12 | 1 |
----------
| 2 | 20 | 1 |
----------
| 3 | 03 | 2 |
----------
| 4 | 55 | 3 |
----------
| 5 | 11 | 3 |
So, all the rows that satisfied the criteria until an exception to the
criteria occurred became part of a group. The numbering of the group
doesn't necessarily need to follow this pattern, I just felt like this was
a logical/simple numbering to explain the solution I am looking for.

No comments:

Post a Comment