Find Closest Numeric Value in Database

Find closest numeric value in database

get the difference between the area and your input, take absolute value so always positive, then order ascending and take the first one

SELECT TOP 1 * FROM [myTable] 
WHERE Name = 'Test' and Size = 2 and PType = 'p'
ORDER BY ABS( Area - @input ) 

SQL: Find closest number to given value with ties

If your driver supports nested queries:

SELECT * 
FROM CARS 
WHERE ABS(price - $price) = ( SELECT MIN(ABS(price - $price)) FROM CARS )

SQL Get closest value to a number

There are a couple of answers here but they both use ctes/complex subqueries. There is a much simpler/faster way by just doing a couple of self joins and a group-by

https://www.db-fiddle.com/f/rM268EYMWuK7yQT3gwSbGE/0

select 
      min(min.quantity) as minQuantityOverDivide
    , t1.divide
    , max(max.quantity) as maxQuantityUnderDivide
    , case 
        when 
            (abs(t1.divide - coalesce(min(min.quantity),0)) 
            <
            abs(t1.divide - coalesce(max(max.quantity),0)))
        then max(max.quantity)
        else min(min.quantity) end as cloestQuantity

from t1
left join (select quantity from t1) min on min.quantity >= t1.divide
left join (select quantity from t1) max on max.quantity < t1.divide

group by    
    t1.divide

How to get closest value from oracle

Demo setup:

create table demo (a) as 
select column_value
from   table(sys.ora_mining_number_nt(8200.13, 7300.46, 8100.98, 8200.01));

Test:

select min(a)
       keep (dense_rank first
             order by abs(a - 8200.11)) as closest
from   demo;

   CLOSEST
----------
   8200.13

1 row selected.

SQL Fiddle

The first function returns the first value of x in order of y. (There is also a corresponding last function.) More details

If the column is a varchar2 (why?) then the implicit conversion to number should work unless the column contains unexpected values. From 12.2 onwards you could avoid conversion errors with:

select min(a)
       keep (dense_rank first
             order by abs(to_number(a default null on conversion error) - 8200.11)) as closest
from   demo;

Finding the nearest numeric match in a database to what a user has inputted in php

try this

SELECT * FROM `table_test1` where `price` > 80 order by `price` asc limit 1

Find the closest number to value not working (SQL)

You should have a column name in the SELECT clause instead of TOP 1.

Presumably, you want:

SELECT price
FROM products
ORDER BY ABS(price - :price) 
LIMIT 1;

Note that this uses query parameter to pass the target price value rather than concatenating it into the query string. You can have a look at this famous SO post to learn why and how to avoid SQL injection.

Select closest maximal numeric value in Firebird

Let's call num_value your needle (as in, "needle in the haystack") that you're looking for.

First we'll normalize the needle so that it is no lower than the MIN(f_value) and no higher than the MAX(f_value) for each master_id.

Then we'll look for each Detail row with the nearest f_value that's greater than or equal to our normalized needle, grouped by master_id. (This is then just a greatest-n-per-group sql problem).

WITH normalized AS (     -- First normalize the needle for each master_id
  SELECT hilo.master_id,
         MAXVALUE(hilo.lo, MINVALUE(hilo.hi, d.needle)) AS needle
    FROM (SELECT ? FROM rdb$database) d (needle) -- <- change this ? to your needle
         CROSS JOIN
         (SELECT master_id, MAX(f_value), MIN(f_value)
            FROM detail GROUP BY master_id) hilo (master_id, hi, lo)
),
     ranked AS (         -- Next order f_value >= needle by master_id
  SELECT detail.*,
         ROW_NUMBER() OVER (PARTITION BY detail.master_id ORDER BY f_value ASC)
           AS rk
    FROM detail
         LEFT JOIN
         normalized ON detail.master_id = normalized.master_id
   WHERE detail.f_value >= normalized.needle
)
                         -- Strip off the rank ordering and SELECT what you want
SELECT id, master_id, f_value, ...
  FROM ranked
 WHERE rk = 1;

Select closest numerical value with MySQL query

One option would be something along the lines of:

select   the_value,
         abs(the_value - 14) as distance_from_test
from     the_table
order by distance_from_test
limit 1

To select a random record, you can add , rand() to the order by clause. The disadvantage of this method is that you don't get any benefit from indices because you have to sort on the derived value distance_from_test.

If you have an index on the_value and you relax your requirement for the result to be random in the case of ties, you can perform a pair of limited range queries to select the first value immediately above the test value and the first value immediately below the test value and pick whichever is closest to the test value:

(
select   the_value
from     the_table
where    the_value >= 14
order by the_value asc
limit 1
)
union
(
select   the_value
from     the_table
where    the_value < 14
order by the_value desc
limit 1
)
order by abs(the_value - 14)
limit 1

---

Related Topics