Finding the Closest Number / values using SQL Server 2005/2008

Finding the Closest Number / values using SQL Server 2005/2008

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. 

 Previous post I explained about How to RESET identity columns in SQL Server , SQL Server- Case Sensitive Search in SQL Server , SQL Server - Get List of All Table Names in Database , What is sharding - Databases .

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)

-- QUERYDECLARE @input int

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

OUTPUT