Need SQL Query only... Not PL/SQL.

[anandharaj]

Hi

I have ta table with the following col and values:

Table: T1
==========
Co1 Col2
----------
A 10
B 15
C 20
D 05
E 30
F 15
G 25
----------
My criteria is:
================
print all the records from table
where sum(col2) is <=45

It means: logic is something like
col2:=record1+record2+record3+....
Col2:=10+15+20=45
If total of the Col2 is upto 45, i need to print, other wise comeout.

So the sample output should be:
Co1 Col2
----------
A 10
B 15
C 20
=====================

I know that we can write the PL/SQL to get the output using CURSOR.
But, i need to write this Query only by SQL.

Could some one assist me in this regards.

Thanks
Anandharaj

[jmodic]

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

SQL> select * from table1;

C       COL2
- ----------
A         10
B         15
C         20
D          5
E         30
F         15
G         25

7 rows selected.

SQL> SELECT col1, col2 FROM
  2  (SELECT col1, col2,
  3          SUM(col2) OVER (ORDER BY col1
  4          RANGE UNBOUNDED PRECEDING) AS running_total
  5     FROM table1)
  6  WHERE running_total <= 45;

C       COL2
- ----------
A         10
B         15
C         20

SQL>

[anandharaj]

HI Jurij Modic

Thank you very much. This is what I want.

Thanks a lot.

Regards
Anandharaj