TSQL - Efficient method to find where a number falls in a list of values?

This seems simple; I have to be missing something straight forward.

TestTable

Value1 (int)

Value2 (int)

Value3 (int)

Value4 (int)

Insert into TestTable 20, 30, 40 ,50

Value1 = 20

Value2 = 30

Value3 = 40

Value4 = 50

I have the number 37. I want to return 30 and 40 (37 falls between these two numb开发者_如何学Pythoners).

What is the most efficient way using TSQL?

declare @TestTable table (Value1 int, Value2 int, Value3 int, Value4 int)
insert into @TestTable values (20, 30, 40, 50)

declare @Value int = 37

select
  case
    when @Value between Value1 and Value2 then Value1
    when @Value between Value2 and Value3 then Value2
    when @Value between Value3 and Value4 then Value3
    when @Value > Value4 then Value4
  end as Val1,   
  case
    when @Value < Value1 then Value1 
    when @Value between Value1 and Value2 then Value2
    when @Value between Value2 and Value3 then Value3
    when @Value between Value3 and Value4 then Value4
  end as Val2
from @TestTable  

Result:

Val1        Val2
----------- -----------
30          40

SELECT TOP 1 [value] FROM TestTable WHERE [value] <= @number ORDER BY [value] DESC
UNION
SELECT TOP 1 [value] FROM TestTable WHERE [value] >= @number ORDER BY [value]

Alternatively

SELECT
  (SELECT TOP 1 [value] FROM TestTable WHERE [value] <= @number ORDER BY [value] DESC) AS [min],
  (SELECT TOP 1 [value] FROM TestTable WHERE [value] >= @number ORDER BY [value]) AS [max]

Have an index on the [value] field and check if you want >= / <= or >= / < ranges.g

You'd replace the 34 with a variable containing the value you want to find, but I think this should get you what you want.

select
(select top 1 * from TestTable
where value > 34
order by value),

(select top 1 * from TestTable
where value < 34
order by value desc)