I'm attempting to create a query that will find the closest value from one table and return its ID into the resulting table.
Below is an example that should describe the situation better.
Sample Data
These two tables will exist in the SQL database.
Main Table
+----+-------------+
| ID | Measurement |
+----+-------------+
| 1 | 0.24 |
| 2 | 0.5 |
| 3 | 0.14 |
| 4 | 0.68 |
+----+-------------+
Look-up Table
+----+---------------+
| ID | Nominal Value |
+----+---------------+
| 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 |
+----+---------------+
Goal
This will be the result of a query. Measurements should not be on the border (0.25 for example).
+----+-------------+-----------+
| ID | Measurement | Lookup ID |
+----+-------------+-----------+
| 1 | 0.24 | 2 |
| 2 | 0.5 | 5 |
| 3 | 0.14 | 1 |
| 4 | 0.68 | 7 |
+----+-------------+-----------+
Is there a query that would be able to return this kind of result?
Best Answer
A couple of queries tested and optimized for Postgres 9.3. All return the same, all are basically standard SQL, but no RDBMS supports the standard completely.
In particular, the first one uses a
LATERAL JOIN
, which is missing in Oracle or MySQL. Test which performs best.All of them use index-only scans on the
lookup
table in Postgres. Obviously,lookup.nominal_value
needs to be indexed. I suggest to make itUNIQUE
because it seems like the column should be unique, and because that also creates the all-important index automatically.LATERAL JOIN
All parentheses required for
UNION
. Related answer:Postgres 9.2 select multiple specific rows in one query
Correlated subqueries in a subquery
Correlated subqueries in a CTE
Nested correlated subqueries
SQL Fiddle.