Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Finding a closest value from lookup list

user626688Apr 1 2021

Hi, I have a requirement to find an ID from a lookup table based on the closest value. I have a main table and a lookup table. Based on the value from the main table, the query should return an ID of the closest value from the lookup table.
create table measure_tbl(ID number, measure_val number (3,2));
insert into measure_tbl values(1,0.24);
insert into measure_tbl values(2,0.5);
insert into measure_tbl values(3,0.14);
insert into measure_tbl values(4,0.68);
commit;
create table Nom_val_lkp(LKP_ID number, nom_val number(3,2));
insert into Nom_val_lkp values(1,0.1);
insert into Nom_val_lkp values(2,0.2);
insert into Nom_val_lkp values(3,0.3);
insert into Nom_val_lkp values(4,0.4);
insert into Nom_val_lkp values(5,0.5);
insert into Nom_val_lkp values(6,0.6);
insert into Nom_val_lkp values(7,0.7);
insert into Nom_val_lkp values(8,0.8);
insert into Nom_val_lkp values(9,0.9);
commit;
The result should look like:
res.PNGHow can I form a query to find a closest match of the measure value from the lookup table and pick the relevant ID in a query. Please help.

This post has been answered by mathguy on Apr 2 2021
Jump to Answer

Comments

Post Details

Added on Apr 1 2021
8 comments
3,589 views