Creating a Group ID

Database
N/A

Creating a Group ID

Hi,
I have a table with Columns A, B below but I want to create Column C

Column C is explained as follows: Ordering by Column A ASC I want to replicate the value od Column B for all succeeding rows until a Non-Null value is met, then start again.

Column A: Transaction Number
Column B: Transaction Group - Group ID only populated for first transaction in the group
Column C: Trans_ID - the field I want to create

A B C

1 1 1
2 ? 1
3 ? 1
4 2 2
5 3 3
6 ? 3
7 ? 3
8 ? 3
9 ? 3
10 4 4
11 ? 4

Thanks,
Marty
Tags (4)
2 REPLIES
N/A

Re: Creating a Group ID

Hi Marty,
if the values in B are ascending parallel to A (as your sample data) it's simple:
SELECT
a,b,MAX(b) OVER
(ORDER BY a
ROWS UNBOUNDED PRECEDING)
FROM tab

Else in TD13:
SELECT a,b,
MAX(b) OVER
(ORDER BY a
RESET WHEN b IS NOT NULL
ROWS UNBOUNDED PRECEDING)
FROM tab

Pre-TD13 this can be rewritten to:
SELECT
a,b,MAX(b) OVER (PARTITION BY dynamic_partition)
FROM
(
SEL a,b,
SUM(CASE WHEN b IS NOT NULL THEN 1 ELSE 0 END)
OVER (ORDER BY a
ROWS UNBOUNDED PRECEDING) AS dynamic_partition
FROM tab
) AS dt

Both versions will explain exactly the same.

Dieter
N/A

Re: Creating a Group ID

Thank you Dieter - that works perfectly. Great Help.
Marty