Oracle select similar values

Question

i have a database table with a lot of values like this: 340.13 and 232.89.

Now i want to select the value with the best match with a comparison value.

Is this possible without great effort?

Answer 1

This will match values that are within +-10% of the search value and, if there are multiple values, will find the closest match by absolute difference.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TABLE_NAME ( VALUE ) AS
SELECT 340.13 FROM DUAL UNION ALL
SELECT 232.89 FROM DUAL UNION ALL
SELECT 224.73 FROM DUAL UNION ALL
SELECT 100.00 FROM DUAL;

Query 1:

WITH search_values ( search_value ) AS (
  SELECT 330 FROM DUAL UNION ALL
  SELECT 230 FROM DUAL
)
SELECT search_value,
       value
FROM   (
  SELECT search_value,
         value,
         RANK() OVER ( PARTITION BY Search_value 
                       ORDER BY ABS( value - search_value ) ) AS rnk
  FROM   table_name t
         INNER JOIN
         search_values v
         ON ( t.value BETWEEN search_value * 0.9 AND search_value * 1.1 )
)
WHERE  Rnk = 1

Results:

| SEARCH_VALUE |  VALUE |
|--------------|--------|
|          230 | 232.89 |
|          330 | 340.13 |