To populate the values from another column based on serial no

Analytics

To populate the values from another column based on serial no

Hello All,

 

My table as below:

SNOCOL 1COL 2
9A1M
10A1N
11A1F
8A1H
12A1Q

 

Am expecting output as below:

COL 1COL 2NEXT VALUE
A1MN
A1NF
A1FQ
A1HM
A1QNULL

 

Next value is the new column to be populated based on sequence number of col 2.

In expected output, first row N comes with below logic:

Col 2 1st row value is M. Its corresponding sequence number is 9. So, 1st row next value should be populated with seq number 10's column 2 value which is N.

 

I tried using coalesce min with partition by row preceeding but am not getting the expected output.

 

Would be more helpful if someone can give the SQL query for the same.

 

Thanks in advance!

 

  • Analytic function
Tags (1)

Accepted Solutions
Supporter

Re: To populate the values from another column based on serial no

good old outer join can do

select c.col1, c.col2, n.col2
from mytab c
        left outer join
        mytab n
           on c.sno + 1 = n.sno
                and c.col1 = n.col1

or an olap function

select col1, col2, max(col2) over (partition by col1 order by sno rows between 1 following and 1 following)
from mytab
1 ACCEPTED SOLUTION
2 REPLIES
Supporter

Re: To populate the values from another column based on serial no

good old outer join can do

select c.col1, c.col2, n.col2
from mytab c
        left outer join
        mytab n
           on c.sno + 1 = n.sno
                and c.col1 = n.col1

or an olap function

select col1, col2, max(col2) over (partition by col1 order by sno rows between 1 following and 1 following)
from mytab

Re: To populate the values from another column based on serial no

Thanks in ton Ulrich