CODEWITHSUNDEEP
t-sqlgroup-bypartitioningrowset
mac
mac

Reputation: 65

SQL Renumbering index after group by

I have the following input table:

Seq         Group      GroupSequence
1           0           
2           4           A
3           4           B
4           4           C
5           0           
6           6           A
7           6           B
8           0

Output table is:

Line        NewSeq     GroupSequence
1           1           
2           2           A
3           2           B
4           2           C
5           3           
6           4           A
7           4           B
8           5

The rules for the input table are:

  1. Any positive integer in the Group column indicates that the rows are grouped together. The entire field may be NULL or blank. A null or 0 indicates that the row is processed on its own. In the above example there are two groups and three 'single' rows.

  2. the GroupSequence column is a single character that sorts within the group. NULL, blank, 'A', 'B' 'C' 'D' are the only characters allowed.

  3. if Group has a positive integer, there must be alphabetic character in GroupSequence.

I need a query that creates the output table with a new column that sequences as shown. External apps needs to iterate through this table in either Line or NewSeq order(same order, different values)

I've tried variations on GROUP BY, PARTITION BY, OVER(), etc. WITH no success. Any help much appreciated.

Upvotes: 0

Views: 176

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Perhaps this will help

The only trick here is Flg which will indicate a new Group Sequence (values will be 1 or 0). Then it is a small matter to sum(Flg) via a window function.

Edit - Updated Flg method

Example

Declare @YourTable Table ([Seq] int,[Group] int,[GroupSequence] varchar(50))
Insert Into @YourTable Values 
 (1,0,null)
,(2,4,'A')
,(3,4,'B')
,(4,4,'C')
,(5,0,null)
,(6,6,'A')
,(7,6,'B')
,(8,0,null)

Select Line = Row_Number() over (Order by Seq)
      ,NewSeq = Sum(Flg) over (Order By Seq)
      ,GroupSequence
 From  (
        Select * 
              ,Flg =  case when [Group] = lag([Group],1) over (Order by Seq) then 0 else 1 end
         From  @YourTable
       ) A
 Order By Line

Returns

Line    NewSeq  GroupSequence
1       1       NULL
2       2       A
3       2       B
4       2       C
5       3       NULL
6       4       A
7       4       B
8       5       NULL

Upvotes: 1

Related Questions