Get the next minimum greater than or equal to the set value for each group

Question

considering the following table 1:

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 RefID and intVals, such as

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

an SQL query is required to get the next large intValue for each RefID and NULL, if not found in table 1, the 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 1

The derived table a extracts the minimum values ​​from table 1, the data refid and intVal from intVal ; an external request receives only someValue.

 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's the Sql Fiddle with live test .

Answer 2

You can solve this problem with the ROW_NUMBER() function:

 SELECT RefID, intVal, NextGt, SomeVal, FROM ( SELECT t2.RefID, t2.intVal, t1.intVal AS NextGt, t1.SomeVal, ROW_NUMBER() OVER (PARTITION BY t2.RefID, t2.intVal ORDER BY t1.intVal) AS rn FROM dbo.Table2 AS t2 LEFT JOIN dbo.Table1 AS t1 ON t1.RefID = t2.RefID AND t1.intVal >= t2.intVal ) s WHERE rn = 1 ; 

The derived table corresponds to each row of Table2 with all rows of Table1 that have the same RefID and intVal , which is greater than or equal to Table2.intVal . Each subset of matches is ranked, and the first row is returned by the main query.

A nested query uses an outer join, so those Table2 rows that do not have Table1 matches are still returned (with zeros replaced by Table1 columns).

Alternatively, you can use OUTER APPLY :

 SELECT t2.RefID, t2.intVal, t1.intVal AS NextGt, t1.SomeVal FROM dbo.Table2 AS t2 OUTER APPLY ( SELECT TOP (1) t1.intVal FROM dbo.Table1 AS t1 WHERE t1.RefID = t2.RefID AND t1.intVal >= t2.intVal ORDER BY t1.intVal ASC ) AS t1 ; 

This method is simpler: for each row of Table2 get all the matches from Table1 based on the same set of conditions, sort the matches in ascending order of Table1.intVal and take the top intVal .

Answer 3

This can be done using the join, group by and case command and the trick:

 select t1.refid, t2.intval, min(case when t1.intval > t2.intval then t1.intval end) as min_greater_than_ref, substring(min(case when t1.intval > t2.intval then right('00000000'+cast(t1.intval as varchar(255)), 8)+t1.SomeVal) end)), 9, 1000) from table1 t1 left join table2 t2 on t1.refid = t2.refid group by t1.refid, t2.intval 

SO, the trick is to add an integer value to SomeValue, zero adding an integer value (in this case, up to 8 characters). You get something like: "00000020val01". The minimum in this column is based on the minimum of an integer. The last step is to extract the value.

In this example, I used SQL Server syntax to concatenate. In other databases, you can use CONCAT () or ||.