Need help in DB2 Query to group & rank records based on key & data val

[bschand]

Consider an user DB2 table, Traffic_Dtl:
Key_col Total_col Group_col
K1 750
K2 130
K3 100
K4 790
K5 500
K6 470
Where group_col is null in all the rows.
I have threshold limit 1000 and individual Count_col value always below 1000 only. I need to group the records and update each group with similar group value in Group_col. So that each group has number of records with sum of count_col is always less than or equal to 1000. For example the outcome of grouping should be:
Key_col Total_col Group_col
K1 750 1
K2 130 1
K3 100 1
K4 790 2
K5 500 3
K6 470 3
Any help in this regard is greatly appreciated.

Thanks.

 

[MarcLodge]

bschand,

Could you be a little clearer? It appears that you wish the third column 'Group_col' to be some sort of ranking, but I do not understand why Key_col column with a value of K1, K2, and K3 are ranked together.

Marc

 

[blom0344]

I'd say a stored procedure with a least 2 cursors would be needed. Too long ago for me to give you the the syntax, but I think you need to loop through the records ordered by the first column and iterate the first cursor with total_col as long as the value <1000. The second one should keep track of the ranking value (increment with 1)

Ties Blom