Need SQL Query only... Not PL/SQL.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Feb 2001
    Location
    Atlanta, USA
    Posts
    131

    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Feb 2001
    Location
    Atlanta, USA
    Posts
    131
    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
  •  


Click Here to Expand Forum to Full Width