CODEWITHSUNDEEP
sqldb2numberssequencedb2-400
user3663615
user3663615

Reputation: 41

sequence number generation in db2

I have table A which contains 5 columns col1 to col5.totally it contains 6 rows.I am using DB2 sql

Below is the data for col2.

A
A
test
testasfdla
Null
Null

Requirement:- If col2 contains null i need assign sequence number starting with 1.

excepted o/p:-

Below is the data for col2.

A
A
test
testasfdla
1
2

I tried with row_number but did not get the required o/p.

Upvotes: 1

Views: 1208

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Try this:

WITH T (C) AS 
(
VALUES
  'A'
, 'A'
, 'test'
, 'testasfdla'
, Null
, Null
)
SELECT COALESCE(C, TO_CHAR(ROW_NUMBER() OVER (PARTITION BY C)))
FROM T

Upvotes: 1

Related Questions