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 !
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.
You can solve this using 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 matches each Table2
row with all Table1
rows that have the same RefID
and an intVal
that is greater than or equal to Table2.intVal
. Each subset of matches is ranked and the first row is returned by the main query.
The nested query uses an outer join, so that those Table2
rows that have no Table1
matches are still returned (with nulls substituted for the 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 arguably more straightforward: for each Table2
row, get all matches from Table1
based on the same set of conditions, sort the matches in the ascending order of Table1.intVal
and take the topmost intVal
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With