Select closest maximal numeric value in Firebird

Imagine there’re 2 tables, let’s call them “Master” and “Detail”:

Master
--------------------------------
| ID | field_1 | ... | field_n |
--------------------------------

Detail
--------------------------------------------
| ID | master_id | f_value | ... | field_n |
--------------------------------------------
| 1  |      1    |   0.03  | ... |   ...   |
--------------------------------------------
| 2  |      1    |   0.95  | ... |   ...   |
--------------------------------------------
| 3  |      1    |   1.22  | ... |   ...   |
--------------------------------------------
| 4  |      2    |   0.91  | ... |   ...   |
--------------------------------------------
| 5  |      2    |   0.93  | ... |   ...   |
--------------------------------------------
| 6  |      2    |   2.07  | ... |   ...   |
--------------------------------------------

There’re 2 input parameters: list of Master IDs (master_id_list) and numeric value (num_value).

For every ID in master_id_list I should get one Detail record:

1. If num_value < MIN( f_value ), it should be the record with MIN( f_value )
2. If num_value > MAX( f_value ), it should be the record with MAX( f_value )
3. Otherwise it should be the record with the closest maximal f_value

Example1. master_id_list = [ 1, 2 ], num_value = 0. Result:

--------------------------------------------
| 1  |      1    |   0.03  | ... |   ...   |
--------------------------------------------
| 4  |      2    |   0.91  | ... |   ...   |
--------------------------------------------

Example2. master_id_list = [ 1, 2 ], num_value = 50. Result:

--------------------------------------------
| 3  |      1    |   1.22  | ... |   ...   |
--------------------------------------------
| 6  |      2    |   2.07  | ... |   ...   |
--------------------------------------------

Example3. master_id_list = [ 1, 2 ], num_value = 0.94. Result:

--------------------------------------------
| 2  |      1    |   0.95  | ... |   ...   |
--------------------------------------------
| 6  |      2    |   2.07  | ... |   ...   |
--------------------------------------------

Is it possible with one single SQL query? I’ve tried to “play” with solutions here and here but failed.

Answer

Let’s call num_value your needle (as in, “needle in the haystack”) that you’re looking for.

First we’ll normalize the needle so that it is no lower than the MIN(f_value) and no higher than the MAX(f_value) for each master_id.

Then we’ll look for each Detail row with the nearest f_value that’s greater than or equal to our normalized needle, grouped by master_id. (This is then just a greatest-n-per-group sql problem).

WITH normalized AS (     -- First normalize the needle for each master_id
  SELECT hilo.master_id,
         MAXVALUE(hilo.lo, MINVALUE(hilo.hi, d.needle)) AS needle
    FROM (SELECT ? FROM rdb$database) d (needle) -- <- change this ? to your needle
         CROSS JOIN
         (SELECT master_id, MAX(f_value), MIN(f_value)
            FROM detail GROUP BY master_id) hilo (master_id, hi, lo)
),
     ranked AS (         -- Next order f_value >= needle by master_id
  SELECT detail.*,
         ROW_NUMBER() OVER (PARTITION BY detail.master_id ORDER BY f_value ASC)
           AS rk
    FROM detail
         LEFT JOIN
         normalized ON detail.master_id = normalized.master_id
   WHERE detail.f_value >= normalized.needle
)
                         -- Strip off the rank ordering and SELECT what you want
SELECT id, master_id, f_value, ...
  FROM ranked
 WHERE rk = 1;