SQL script for check Sequence_No?

Question

I need to check the sequence number generated of the client per risk , per policy ( E.g. starts at 1 for each risk and is incremented by 1 for each client) in table1. Assign sequence number 1 if the Client is having the Role_ID as ‘A’ in Table2 and assign 2 if the Client is having Role_id as ‘B’ .
After that table 1 will look like

policy       risk          Risk_Sequence_No          Client_Id          Tab2.role_id
---------------------------------------------------------------------------------------
989898989      1                  1                  1000000001        A
989898989      1                  2                  1000000002        B
989898989      2                  1                  1000000001        A
989898989        2                2                  1000000002        B
989898989      3                  1                  1000000001        A
170023233      1                  1                  1000000004        A
170023233      1                  2                  1000000005        B

Table 2 :

policy Client_Id role_id

989898989     1000000001                 A
989898989     1000000002                 B
170023233     1000000004                 A
170023233       1000000005                 B

There is no role_id in your example.

user330315
– user330315

2014-09-18 13:13:57 +00:00
Commented Sep 18, 2014 at 13:13 — user330315
– user330315, Commented Sep 18, 2014 at 13:13

user330315 · Accepted Answer · 2014-09-18 13:13:13Z

1

There is no role_id in your example, but the general solution to this kind of problems are window functions:

select policy,
       risk,
       row_number() over (partition by policy order by client_id) as risk_sequence_no
       client_id
from the_table
order by policy, risk, client_id;

answered Sep 18, 2014 at 13:13

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Avi Over a year ago

Hi, thanks for the solution. But this is not giving me correct results. as in table Risk_Sequence_No can only be 1 or 2 with condition as sequence number 1 if the Client is having the Role_ID as ‘A’ in Table2 and assign 2 if the Client is having Role_id as ‘B’ . The Role_ID will be taking by joining table 1 and table 2.

user330315 Over a year ago

@Avi: please add the role_id to your example

Avi Over a year ago

Hi, Thanks for help. Added table 2 for refrence.

Collectives™ on Stack Overflow

SQL script for check Sequence_No?

policy Client_Id role_id

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

policy Client_Id role_id

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related