5 Replies Latest reply on Oct 5, 2012 7:30 PM by 812975

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

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
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
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
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

• ###### 4. Re: Find number table B > = number in table A but < then next entry in B
Hi,
``````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``````
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
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