Find the Closest Number using SQL Server

In one of the queries I was writing, I had to find out the closest match to a given value. In my case, it was a Price column and given a value, I had to find the prices of items that closely match it.

Here’s how it can be done using the SQL Server ABS function

-- SAMPLE DATA
DECLARE @TT TABLE (ID int, Price float)
INSERT INTO @TT VALUES (1, 23.29)
INSERT INTO @TT VALUES (2, 91.33)
INSERT INTO @TT VALUES (3, 78.45)
INSERT INTO @TT VALUES (4, 25.26)
INSERT INTO @TT VALUES (5, 11.13)
INSERT INTO @TT VALUES (6, 3.22)
INSERT INTO @TT VALUES (7, 29.33)
INSERT INTO @TT VALUES (8, 88.34)
INSERT INTO @TT VALUES (9, 48.44)
INSERT INTO @TT VALUES (10, 38.39)

-- QUERY
DECLARE @input int
SET @input = 25
SELECT TOP 3 ID, Price from @TT
ORDER BY ABS(Price - @input)

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook