CODEWITHSUNDEEP
sqldatabaseteradata
Aritra Bhattacharya
Aritra Bhattacharya

Reputation: 780

Setting Counter value specific to an id column based on an input chunk value in teradata

I have a table having the below data distribution : 

cola colb 
id1     a
id1     b
id2     c
id2     d
id3     e
id3     f
id4     g
id4     h

I am trying to create an additional id field based on a chunk value which I will be passing as input : for eg 2 in this case. So, my end result would look something like this : 

cola colb col_new_id
id1     a        1
id1     b        1
id2     c        1
id2     d        1
id3     e        2
id3     f        2
id4     g        2
id4     h        2

Is there a way to handle it using window/analytical functions in teradata. We can always loop and generate the value of col3 based on col1 values, but I am looking for a query level solution without looping. Will it be possible.

Upvotes: 0

Views: 74

Answers (1)

Nick
Nick

Reputation: 147196

You can use DENSE_RANK() over the cola values to generate a number which represents the order of that value in the list then add chunk - 1 and integer divide by chunk to create the col_new_id value. For example:

DECLARE @chunk INT;
SET @chunk = 2;
SELECT cola, colb, 
       (DENSE_RANK() OVER (ORDER BY cola) + @chunk - 1) / @chunk AS col_new_id
FROM data

Output:

cola    colb    col_new_id
id1     a       1
id1     b       1
id2     c       1
id2     d       1
id3     e       2
id3     f       2
id4     g       2
id4     h       2

Demo on dbfiddle

Upvotes: 1

Related Questions