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:
- If
num_value < MIN( f_value ), it should be the record withMIN( f_value ) - If
num_value > MAX( f_value ), it should be the record withMAX( f_value ) - 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.
Advertisement
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 wantSELECT id, master_id, f_value, FROM ranked WHERE rk = 1;