3

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.

Saulo Silva
  • 31
  • 2

2 Answers2

2

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");
CL.
  • 173,858
  • 17
  • 217
  • 259
1

You can try a raw query:

SELECT * FROM table WHERE abs(Value1 - arg) = (SELECT min(abs(Value1 - arg)) FROM table)

Here arg is the input value.

S.D.
  • 29,290
  • 3
  • 79
  • 130