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

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: