1

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.

Old Skull
  • 165
  • 7
  • I'm confused. Nothing is called `num_value` in your sample data. – Gordon Linoff Feb 07 '20 at 20:28
  • @GordonLinoff, ```num_value``` is the input parameter for ```f_value```. If ```num_value``` lies between ```MIN( f_value )``` and ```MAX( f_value )```, I need to find the first ```f_value >= num_value```. Otherwise I need to get either ```MIN( f_value )``` or ```MAX( f_value )```. – Old Skull Feb 08 '20 at 07:36

2 Answers2

2

You should be able to use a correlated subquery. Assuming that num_value is in the master table and f value is in the detail table:

select m.*,
       (select first 1 d.f_value
        from detail d
        where d.master_id = m.master_id
        order by abs(m.num_value - d.f_value)
       )
from master m;

EDIT:

If you want a preference for the greater value -- if it exists -- just change the order by to:

order by (case when d.f_value >= m.num_value then 1 else 2 end),
         abs(d.f_value - m.num_value)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • ```order by abs(m.num_value - d.f_value)``` would find closest but not **maximal** closest. E.g. you have ```f_value = [ 0.76, 0.85, 0.93, 1,98, 50.23 ]``` and need to find closest maximal to ```0.94```. The result will be ```0.93``` instead of ```1.98``` – Old Skull Feb 08 '20 at 07:42
  • @OldSkull . . . Is that what you mean by "maximal closest"? I figured it was just a way of saying "closest" with emphasis. You can use `order by (case when d.f_value >= m.num_value then 1 else 2 end), abs(m.num_value - d.f_value) asc`. This is still much simpler that the alternative answer. I don't interpret "maximal" as being greater than a particular value, so I misinterpreted that part of the question. – Gordon Linoff Feb 08 '20 at 15:26
  • Thank you. It indeed looks simpler. Sorry for stupid question: how can I get full row (d.*), not only ```f_value```? – Old Skull Feb 08 '20 at 18:28
  • 1
    @OldSkull . . . The simplest method is to `JOIN` back to the `details` table. – Gordon Linoff Feb 08 '20 at 18:31
  • thank you. I've finally managed to create working sql. Not sure about its ideological correctness, but it seems to work as expected. Would it be right/necessary to add it to my original question? – Old Skull Feb 09 '20 at 18:23
2

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;
pilcrow
  • 56,591
  • 13
  • 94
  • 135
  • Thank you. It seems to work. But now I wonder: is that stuff with ```rdb$database``` necessary. I've replaced it with ```SELECT "hilo"."master_id", MAXVALUE( "hilo"."lo", MINVALUE( "hilo"."hi", :needle ) ) AS "needle" FROM ( SELECT "master_id", MIN( "q_nom" ), MAX( "q_nom" ) FROM "detail" GROUP BY "master_id" ) "hilo" ( "master_id", "lo", "hi" )``` and it seems works too. – Old Skull Feb 08 '20 at 10:54
  • @OldSkull, well no one knew about the existence of "q_nom" until your comment. In any case, don't use this answer, use Gordon Linoff's answer below. – pilcrow Feb 09 '20 at 04:21
  • ```q_nom``` is ```f_value```, they are from test and work tables – Old Skull Feb 09 '20 at 17:29