SQL & PL/SQL

Hi Friends,

I have now been struggling for three days trying to figure out a way to write an SQL (not PL/SQL) query to acheive the result as described in the sample problem:

Table A rows:

ID SAL
1 11
1 5
1 6
1 3
1 2
1 1
2 14
2 14
2 5

Output Required:

ID SAL RNK
1 11 1
1 6 2
1 5 2
1 3 3
1 2 3
1 1 3
2 14 1
2 14 1
2 5 2

The output required is as follows:
1. For each ID group we order the rows by SAL.
2. Then make group of rows starting from the first row in such a way that the sum of SAL in that group of rows is atleast equal to 10 (it can be more). In other words, we pick only those many rows in a group that add to =>10. As soon as the sum of SAL is >=10, the group is done and the next row goes to next group.
3. And, then we rank all the groups and give the same rank to all the rows in that group.

Explained for the sample here, Since the 1st row itself has SAL >=10, it becomes the first group. Then the 2nd and 3rd row total to SAL >=10 so it becomes group 2 and both the rows are given RNK 2. Similarly, the next three rows fall under group 3 (although the sum is still <10, but there are no rows remamining for ID 1.

There is one exception, that the same SAL always gets the same RNK.

I have tried LEAD/LAG, PARTITION BY, DENSE RANK etc. but could not find the way to write the query. I hope that this forum is the write place to look for any help from the experts.

Would appreciate any help that you can do for me.
Goga