Solution to TSQL Beginners Challenge5

DECLARE @T TABLE(Numbers INT)
INSERT INTO @t
SELECT 10 UNION ALL
SELECT 12 UNION ALL
SELECT 19 UNION ALL
SELECT 25 UNION ALL
SELECT 25 UNION ALL
SELECT 34 UNION ALL
SELECT 38

DECLARE @NumberToSearch INT
SET @NumberToSearch = 24;

WITH X (Numbers, Rn)
AS
(
	SELECT TOP 100 PERCENT
		Numbers,
		DENSE_RANK() Over (ORDER BY ABS(@NumberToSearch - Numbers)) AS Rn
	FROM @t
	GROUP BY Numbers
	ORDER BY Rn
)
SELECT Numbers AS [Closest Number]
FROM X
WHERE Rn=1;

Tags: