Geek asks and answers

Find a row from the database depending on the value that is closest to the given value

I want to find a table row from a database depending on the value that is closest to the given value.

I have the following data

Id  Rate         Fat
1   10           8.00
2   20           8.10
3   30           8.20
4   40           8.30
5   50           8.34
6   60           8.40
7   50           8.36

Suppose a user wants to search with Fat

For 8.0 it should return

Id  Rate         Fat
1   10           8.00

For 8.06 he must return

Id  Rate         Fat
2   20           8.10

For 8.35, it should return 8.34 instead of 8.36 (although the difference is the same, it should give preference to a lower value if the difference is the same)

Id  Rate         Fat
5   60           8.34
+3
source share
1 answer

In SQL Server you can do it

select top 1 *
from T
order by abs(Fat - 8.35), Fat

I guess this syntax for mysql

select *
from T
order by abs(Fat - 8.35), Fat
limit 1
+5
source

More articles:


All Articles