clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36246 in the last week).

CREATE TABLE measurement(id int, measurement numeric); INSERT INTO measurement (id, measurement) VALUES (1, 0.24) , (2, 0.5) , (3, 0.14) , (4, 0.68); CREATE TABLE lookup (id int, nominal_value numeric UNIQUE); -- automatically indexed INSERT INTO lookup(id, nominal_value) VALUES (1, 0.1) , (2, 0.2) , (3, 0.3) , (4, 0.4) , (5, 0.5) , (6, 0.6) , (7, 0.7) , (8, 0.8) , (9, 0.9);
4 rows affected
9 rows affected
 hidden batch(es)


-- LATERAL SELECT m.id, m.measurement, l.nominal_value FROM measurement m JOIN LATERAL ( ( SELECT nominal_value - m.measurement AS diff, nominal_value FROM lookup WHERE nominal_value >= m.measurement ORDER BY nominal_value LIMIT 1 ) UNION ALL ( SELECT m.measurement - nominal_value, nominal_value FROM lookup WHERE nominal_value <= m.measurement ORDER by nominal_value DESC LIMIT 1 ) ORDER BY 1 LIMIT 1 ) l ON true;
id measurement nominal_value
1 0.24 0.2
2 0.5 0.5
3 0.14 0.1
4 0.68 0.7
 hidden batch(es)


-- Correlated subqueries in subquery SELECT id, measurement , CASE WHEN hi - measurement > measurement - lo THEN lo ELSE hi END AS nominal_value FROM ( SELECT id, measurement , ( SELECT nominal_value FROM lookup WHERE nominal_value >= m.measurement ORDER BY nominal_value LIMIT 1) AS hi , COALESCE(( SELECT nominal_value FROM lookup WHERE nominal_value <= m.measurement ORDER by nominal_value DESC LIMIT 1), 0) AS lo -- cover possible NULL values FROM measurement m ) sub;
id measurement nominal_value
1 0.24 0.2
2 0.5 0.5
3 0.14 0.1
4 0.68 0.7
 hidden batch(es)


-- Correlated subqueries in CTE WITH cte AS ( SELECT id, measurement , ( SELECT nominal_value FROM lookup WHERE nominal_value >= m.measurement ORDER BY nominal_value LIMIT 1) AS hi , COALESCE(( SELECT nominal_value FROM lookup WHERE nominal_value <= m.measurement ORDER by nominal_value DESC LIMIT 1), 0) AS lo -- cover possible NULL values FROM measurement m ) SELECT id, measurement , CASE WHEN hi - measurement > measurement - lo THEN lo ELSE hi END AS nominal_value FROM cte;
id measurement nominal_value
1 0.24 0.2
2 0.5 0.5
3 0.14 0.1
4 0.68 0.7
 hidden batch(es)


-- Nested correlated subqueries SELECT id, measurement , (SELECT nominal_value FROM ( ( SELECT nominal_value - m.measurement, nominal_value FROM lookup WHERE nominal_value >= m.measurement ORDER BY nominal_value LIMIT 1 ) UNION ALL ( SELECT m.measurement - nominal_value, nominal_value FROM lookup WHERE nominal_value <= m.measurement ORDER by nominal_value DESC LIMIT 1 ) ORDER BY 1 LIMIT 1 ) sub ) AS nominal_value FROM measurement m;
id measurement nominal_value
1 0.24 0.2
2 0.5 0.5
3 0.14 0.1
4 0.68 0.7
 hidden batch(es)