Tester101 - 4 months ago 16

SQL Question

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

Source (Stackoverflow)

Comments