Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Assigning a "Group ID" based on consecutive Values Expand / Collapse
Author
Message
Posted Thursday, July 31, 2014 2:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:21 PM
Points: 2, 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# Data
1 A
2 B
3 B
4 A
5 B
6 B
7 A
8 A
9 A

I 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 GroupID
1 A 1
2 B 2
3 B 2
4 A 3
5 B 4
6 B 4
7 A 5
8 A 5
9 A 5

You'll notice that there are only two values in DATA but whenever there is a flip between them, the GroupID increments.

Any suggestions?


Post #1598492
Posted Thursday, July 31, 2014 2:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 2,181, Visits: 5,906
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 GroupID
FROM PRIME_GROUP PG;

Results
R  D  GroupID
-- -- -------
1 A 1
2 B 2
3 B 2
4 A 3
5 B 4
6 B 4
7 A 5
8 A 5
9 A 5
Post #1598498
Posted Thursday, July 31, 2014 3:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:21 PM
Points: 2, Visits: 1
Thanks! Now I have to go learn how you did some of this. Thanks a ton!
Post #1598517
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse