Consider table x

id,val
1,100
3,300

And table y

id
1
2
3

For each row of y I want the val from x where the id from y is equal or is the closest before the id from x like that:

id,val
1,100
2,100
3,300

I tried to find the closest id with correlated subquery:

WITH 
x AS (SELECT * FROM (VALUES (1, 100),(3, 300)) AS t(id, val)),
y AS (SELECT * FROM (VALUES 1,2,3) AS t(id))
SELECT *, (
    SELECT x.id
    FROM x
    WHERE x.id <= y.id
    ORDER BY x.id DESC
    LIMIT 1
) as closest_id
FROM y

But I get

SYNTAX_ERROR: line 5:5: Given correlated subquery is not supported

I also tried with a left join:

SELECT *
FROM y
LEFT JOIN x ON x.id <= (
    SELECT MAX(xbis.id) FROM x AS xbis WHERE xbis.id <= y.id
)

But I get the error

SYNTAX_ERROR: line 7:5: Correlated subquery in given context is not supported

CodePudding user response：

You can try joining based on less then condition and then group the results and find needed data from the grouping:

WITH 
x AS (SELECT * FROM (VALUES (1, 100),(3, 300),(4, 400)) AS t(id, val)),
y AS (SELECT * FROM (VALUES 1,2,3,4) AS t(id))

SELECT y.id as yId,
    max(x.id) as xId,
    max_by(x.val, x.id) as val
FROM y
JOIN x on x.id <= y.id
GROUP BY y.id
ORDER BY y.id

Output:

CodePudding user response：

You use like this i think its work...

SELECT *, (
    SELECT top 1  x.val
    FROM x
    WHERE x.id <= y.id
    ORDER BY x.id DESC
    
) as closest_id
FROM y