## Assigning a "Group ID" based on consecutive Values

 Author Message Tony.Valenti SSC Rookie Group: General Forum Members Points: 42 Visits: 1 Hi Guys,I have an interesting problem I am trying to solve without using cursors...I have a data set that looks something like like this:Row# Data1 A2 B3 B4 A5 B6 B7 A8 A9 AI need wanting to assign a group ID to the data based on consecutive values. Here's what I need my data to look like:Row# Data GroupID1 A 12 B 23 B 24 A 35 B 46 B 47 A 58 A 59 A 5You'll notice that there are only two values in DATA but whenever there is a flip between them, the GroupID increments.Any suggestions? Eirikur Eiriksson SSC-Forever Group: General Forum Members Points: 40965 Visits: 19479 Straight forward running total and lag`;WITH BASE AS (SELECT [R], [D]FROM (VALUES (1,'A'),(2,'B'),(3,'B'),(4,'A'),(5,'B'),(6,'B'),(7,'A'),(8,'A'),(9,'A')) AS X([R],[D])),PRIME_GROUP AS( SELECT B.R ,B.D ,CASE WHEN LAG(B.D,1,'') OVER (ORDER BY B.R) = B.D THEN 0 ELSE 1 END AS GR_ID FROM BASE B)SELECT PG.R ,PG.D ,SUM(PG.GR_ID) OVER ( PARTITION BY (SELECT NULL) ORDER BY PG.R ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS GroupIDFROM PRIME_GROUP PG;`Results`R D GroupID-- -- -------1 A 12 B 23 B 24 A 35 B 46 B 47 A 58 A 59 A 5` Tony.Valenti SSC Rookie Group: General Forum Members Points: 42 Visits: 1 Thanks! Now I have to go learn how you did some of this. Thanks a ton!