-
Need SQL Query only... Not PL/SQL.
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
Anandharaj
a_anandharaj@yahoo.com
-
Code:
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>
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
HI Jurij Modic
Thank you very much. This is what I want.
Thanks a lot.
Regards
Anandharaj
Anandharaj
a_anandharaj@yahoo.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|