5 Replies Latest reply: Oct 5, 2012 2:30 PM by 812975 RSS

    Find number table B > = number in table A but < then next entry in B

    Withnoe
      I am trying to figure out do the following: Find number table B > = number in table A but < then next entry in B

      Table A

      5
      10
      21
      20

      Table B

      8
      12
      16
      23
      40

      For entry 5 in table A I would like to return 8 from B
      For entry 10 I would lke to return 12 from B
      For entry 21 I would like to return 23 from B
      For entry 20 I would like to return 23 from B

      Edited by: Withnoe on Oct 5, 2012 9:19 AM

      Edited by: Withnoe on Oct 5, 2012 10:54 AM
        • 1. Re: Find number table B > = number in table A but < then next entry in B
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Find number table B > = number in table A but < then next entry in B
            812975
            can you please give us table scripts and sample data? is there any unique constraint? any sorting available? etc?
            • 3. Re: Find number table B > = number in table A but < then next entry in B
              Withnoe
              Table A -- All of the collumns are part of the key

              CLASS # TERM

              BIOL 520 201010
              BIOL 521 201250
              BIOL 522 200980


              Table B All of the collumns are part of the key


              BIOL 520 201110
              BIOL 520 201150
              BIOL 520 201250


              Table A BIOL 520 201010 should match up with BIOL 520 201110 in table B because BIOL 520 201110 row 1 in table B is > than 201010 from table A row 1 but I don't want it to return the second or third row in Collumn B which is what I would get if I used select max term from B where the term from A >= term in B

              We are on Oracle 11G


              Does this help SaadL?
              • 4. Re: Find number table B > = number in table A but < then next entry in B
                Ashu_Neo
                Hi,
                Please check below.
                create table  a (cola number);
                create table  b (colb number);
                insert into a values(5);
                insert into a values(10);
                insert into a values(21);
                insert into a values(20);
                insert into b values(8);
                insert into b values(12);
                insert into b values(16);
                insert into b values(23);
                insert into b values(40);
                
                create or replace function fab ( p1 number)
                return number
                is
                ret number;
                begin
                     for i in (select colb from b where colb >= p1 and rownum <3 order by colb)
                      loop
                           ret := i.colb;
                            exit;
                      end loop;
                      return ret;
                end;
                /
                
                SQL> select fab(cola) from a;
                
                 FAB(COLA)
                ----------
                         8
                        12
                        23
                        23
                NB:- Put requirement in body of your posting in format as mentioned in the link "how to ask a question" of Sb's reply, instead of putting it on thread with hints only.
                It's really helpful. Even I joined recently on otn and read it on the first day and it's helping me a lot till date.. :)

                Thanks!

                Edited by: Ashu_Neo on Oct 5, 2012 10:55 PM
                • 5. Re: Find number table B > = number in table A but < then next entry in B
                  812975
                  CREATE TABLE TAB_A (CLASS# VARCHAR2(10), TERM NUMBER);
                  CREATE TABLE TAB_B (CLASS# VARCHAR2(10), TERM NUMBER);

                  INSERT INTO TAB_A VALUES ('BIOL 520', 201010);
                  INSERT INTO TAB_A VALUES ('BIOL 521', 201250);
                  INSERT INTO TAB_A VALUES ('BIOL 522', 200980);

                  INSERT INTO TAB_B VALUES ('BIOL 520', 201110);
                  INSERT INTO TAB_B VALUES ('BIOL 520', 201150);
                  INSERT INTO TAB_B VALUES ('BIOL 520', 201250);
                  INSERT INTO TAB_B VALUES ('BIOL 521', 201250);
                  INSERT INTO TAB_B VALUES ('BIOL 521', 201260);

                  SELECT MIN(TAB_B.TERM) TERM, TAB_B.CLASS# FROM TAB_A, TAB_B
                  WHERE TAB_A.CLASS# = TAB_B.CLASS#
                  AND TAB_B.TERM > TAB_A.TERM
                  GROUP BY TAB_B.CLASS#;

                  Please let us know if you require something else. Thanks