select statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: select statement

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi Guru's

    I need your help to build sql statement. Please help. I have a table contain two columns.

    COL_1 COL_2
    2 10
    7 5
    8 15
    10 20
    9 25

    I want a query which should check the range(COL_1) first and then return the least (COL_2) within that range of COL_1.

    For Example If I give input for COL_1 as 5 it should return me value of COL_2 5.

    I am using Oracle 8i.

    Thanks In Advance.
    Nagesh

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    set serveroutput on
    create or replace procedure rangs(range number) is
    range1 number;
    range2 number;
    range3 number;
    begin
    select max(col1) into range2 from rang;
    select min(col2) into range1 from rang;
    if range >= range1 and range <= range2 then
    select min(col2) into range3 from rang where col2 <= range;
    end if;
    dbms_output.put_line('least value in col2:'||range3);
    end;

    let me know if this what ur looking at

    regards
    anandkl
    anandkl

  3. #3
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Originally posted by anandkl
    set serveroutput on
    create or replace procedure rangs(range number) is
    range1 number;
    range2 number;
    range3 number;
    begin
    select max(col1) into range2 from rang;
    select min(col2) into range1 from rang;
    if range >= range1 and range <= range2 then
    select min(col2) into range3 from rang where col2 <= range;
    end if;
    dbms_output.put_line('least value in col2:'||range3);
    end;

    let me know if this what ur looking at

    regards
    anandkl
    Thank you very much. I know this can be done in PL/SQL. Actually I am looking for a single select statement. Can it be done?

    Thanks In Advance.
    Nagesh

  4. #4
    Join Date
    Sep 2002
    Posts
    42
    please be more clear

    In GOD I find everything

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by gnagesh
    I want a query which should check the range(COL_1) first and then return the least (COL_2) within that range of COL_1.

    For Example If I give input for COL_1 as 5 it should return me value of COL_2 5.
    TWO values should define a range, shouldn't they?
    If the input is 5 what's the second limit?

    With two limits the thing looks easy:
    Code:
    select min(col_2) from the_table
    where col_1 between :lower_limit and :upper_limit
    Is that you expect?
    Ales
    The whole difference between a little boy and an adult man is the price of toys

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