Finding closest Match data using SQL/PLSQL
Dear Experts,
I need a solution of the following problem using SQL if possible.
CREATE TABLE TESTDATA
(
ID VARCHAR2(20) NOT NULL ,
NAME VARCHAR2(20) ,
SALARY NUMBER ,
CONSTRAINT TESTDATA_PK PRIMARY KEY (ID )ENABLE
);
INSERT INTO TESTDATA (ID, NAME, SALARY) VALUES ('1', 'Tamim', '5000');
INSERT INTO TESTDATA (ID, NAME, SALARY) VALUES ('2', 'Khan', '6000');
INSERT INTO TESTDATA (ID, NAME, SALARY) VALUES ('3', 'Sami', '9000');
INSERT INTO TESTDATA (ID, NAME, SALARY) VALUES ('4', 'Sinthia', '10000');
Commit;
I have issue the following SQL, here Salary between 7000 and 8000 is not exist is the TestData Table.
SQL> Select *
2 from TESTDATA
3 Where Salary between 7000 and 8000;
no rows selected
But need to show the closest value of the salary column if there no row selected.
For example in the above query there is no row selected but I need to display salary with 6000 name Khan (Closest 7000) and 9000 Name Sami (Closest 8000).
How to resolve this issue? Is there any well known standard process in SQL or PLSQL to solve it?
Thanks and Regards
Md.Samiuzzaman Khan (Tamim)
https://tamimdba.wordpress.com/