armen armen - 22 days ago 6
SQL Question

Get next minimum, greater than or equal to a given value for each group

given the following Table1:

RefID intVal SomeVal
----------------------
1 10 val01
1 20 val02
1 30 val03
1 40 val04
1 50 val05
2 10 val06
2 20 val07
2 30 val08
2 40 val09
2 50 val10
3 12 val11
3 14 val12
4 10 val13
5 100 val14
5 150 val15
5 1000 val16


and Table2 containing some RefIDs and intVals like

RefID intVal
-------------
1 11
1 28
2 9
2 50
2 51
4 11
5 1
5 150
5 151


need an SQL Statement to get the next greater intValue for each RefID and NULL if not found in Table1
following is the expected result

RefID intVal nextGt SomeVal
------------------------------
1 11 20 val01
1 28 30 val03
2 9 10 val06
2 50 50 val10
2 51 NULL NULL
4 11 NULL NULL
5 1 100 val14
5 150 150 val15
5 151 1000 val16


help would be appreciated !

Answer

Derived table a retrieves minimal values from table1 given refid and intVal from table2; outer query retrieves someValue only.

select a.refid, a.intVal, a.nextGt, table1.SomeVal
from
(
    select table2.refid, table2.intval, min (table1.intVal) nextGt
      from table2
      left join table1
        on table2.refid = table1.refid
       and table2.intVal <= table1.intVal
     group by table2.refid, table2.intval
) a
-- table1 is joined again to retrieve SomeVal 
left join table1
  on a.refid = table1.refid
 and a.nextGt = table1.intVal

Here is Sql Fiddle with live test.