How can I group repeating records?
I have a single column that denotes whether a record is a start, end or a detail record. Detail Records are records that exist after a start record and before a end record.
I want to group these records together with a unique identifier so that each time there is a end record the next series of records get a new group id.
I've tried a series of self joins, sub queries and cte's Without building a custom function, view or CTE I'm hoping to build this in a single select query.
Any advice or pointers much appreciated.
Example before solution:
--------------------------------------------------
| ID | RecordType | SomeValue
--------------------------------------------------
|001 | Start record | some header info
|002 | Detail Record | value
|003 | Detail Record | value
|004 | Detail Record | value
|005 | End Record | some other header info
|006 | Start Record | some header info
|007 | Detail Record | Value
|008 | End Record | some other header info
What I've trying to achieve:
------------------------------------------------------------------
| ID | RecordType | SomeValue | RecordGroup
------------------------------------------------------------------
|001 | Start record | some header info | 001
|002 | Detail Record | value | 001
|003 | Detail Record | value | 001
|004 | Detail Record | value | 001
|005 | End Record | some other header info| 001
|006 | Start Record | some header info | 002
|007 | Detail Record | Value | 002
|008 | End Record | some other header info| 002
You can use the LAG function and running SUM. The following assumes that there is no partitioning and rows are ordered by ID:
SELECT ID, RecordType, SomeValue,
SUM(chg) OVER (ORDER BY ID) AS grp
FROM (
SELECT ID, RecordType, SomeValue,
CASE WHEN LAG(RecordType) OVER (ORDER BY ID) IN ('Start record', 'Detail Record') THEN 0 ELSE 1 END AS chg
FROM t
) cte1