How to select similar numbers using SQL?

Question

Here are some example numbers:

987
1001
1004
1009
1010
1016
1020
1050

For example, I would like select the top 4 numbers that close to the given number 1009 (so, the results would be 1001, 1004, 1010 and 1016), how should I write the SQL expression?

Answer 1

Try this:

DECLARE @Target int
SET @Target = 1009

SELECT TOP 2 number from TABLE
WHERE number < @Target
ORDER BY number desc
UNION
SELECT TOP 2 number from TABLE
WHERE number > @Target
ORDER BY number asc

Answer 2

Get the distance from the given number by subtracting and using the abs function:

select top 4 Number
from NumberTable
where number <> 1009
order by abs(Number - 1009)

Edit:

As you now mention that you have a very large table, you would need a way to elliminate most of the results first. You could pick the four closest in both direction and then get the correct ones from that:

select top 4 Number
from (
  select Number
  from (
    select top 4 Number
    from NumberTable
    where number < 1009
    order by number desc
  )
  union all
  select Number
  from (
    select top 4 Number
    from NumberTable
    where number > 1009
    order by number
  )
)
order by abs(Number - 1009)

If the numbers are evenly distributed so that you are sure that you can find the numbers in a range like for example +-100 numbers, you can simply get that range first:

select top 4 Number
from (
  select Number
  from NumberTable
  where number between 1009-100 and 1009+100
)
where number <> 1009
order by abs(Number - 1009)

Answer 3

SELECT TOP 4 number
FROM your_table
WHERE number <> @numberToMatch
ORDER BY ABS(number - @numberToMatch)

Answer 4

Taking bits from all other answers on this page!

Assuming an index on the number column this should perform well (at least in SQL Server)

DECLARE @Target int
SET @Target = 1009;

SELECT TOP 4 number 
FROM
(
SELECT TOP 4 number from YourTable
WHERE number < @Target
ORDER BY number desc
UNION ALL
SELECT TOP 4 number from YourTable
WHERE number > @Target
ORDER BY number asc
) d
order by abs(number - @Target)