Newbie: a query to get back the closest number to user input number.

SQL Apprentice Question
have a situation where I need to return the name and salary of an
employee closest to the salary entered by the user.

Is there a "Nearest" or "Closest" type of operator in sql that will
help me instantly get the row that is closest to an arbitrary number?
If not, can anyone help me with an approach to this type of single
table problem?


Much appreciated.


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Is this what you meant to post?

CREATE TABLE Personnel
(emp_name CHAR(25) NOT NULL PRIMARY KEY,
salary_amt DECIMAL (10,2) NOT NULL
CHECK (salary_amt > 0.00));


Try something like this:


SELECT emp_name, salary_amt, @my_salary AS search_salary
FROM Personnel AS P
WHERE ABS(P.salary_amt - @my_salary)
= (SELECT MIN (ABS(P2.salary_amt - @my_salary))
FROM Personnel AS P2);


The closest salary might be higher or lower than the search value, so
you need ABS(). In the real world, there will be a lot of ties, since
most companies have salary schedules rather than odd.