Tester101 Tester101 - 4 months ago 16
SQL Question

Find closest numeric value in database

I need to find a select statement that will return either a record that matches my input exactly, or the closest match if an exact match is not found.

Here is my select statement so far.

SELECT * FROM [myTable]
WHERE Name = 'Test' AND Size = 2 AND PType = 'p'
ORDER BY Area DESC


What I need to do is find the closest match to the 'Area' field, so if my input is 1.125 and the database contains 2, 1.5, 1 and .5 the query will return the record containing 1.

My SQL skills are very limited so any help would be appreciated.

Answer

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 )
Comments