Android : SQLite : Find closest value from a table

Question

I have a SQLite Database on my app and it is setup like this:

id Value1  Value2
1   12.51    10
2   14.62    20
3   18.94    30
4   21.07    40

I would like to have a code to search on the Value1 column for the closest value to the input and return the value of the same row in the Value2 column.

Example:

I have a variable x=15.12, the code should search for the closest value on Value1 (14.62) and return the respective value from Value2 (20)

Or at least give me the row of the closest value.

How can I achieve this? Thank you very much.

Answer 1

Compute the difference, sort by that value, and take only the first result:

SELECT Value2
FROM MyTable
ORDER BY abs(Value1 - ?)
LIMIT 1

In Android, this would be something like this:

cursor = db.query("MyTable", new String[] { "Value2" },
                  null, null, null, null,
                  "abs(Value1 - " + x + ")", "1");