SQL Presto: correlated subquery is not supported

Question

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

Answer 1

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:

yId	xId	val
1	1	100
2	1	100
3	3	300
4	4	400

Answer 2

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