CODEWITHSUNDEEP
t-sql
sugarcrum
sugarcrum

Reputation: 1011

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 numbers).

What is the most efficient way using TSQL?

Upvotes: 0

Views: 187

Answers (3)

user467384
user467384

Reputation: 1167

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)

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 1

Tomalak
Tomalak

Reputation: 338228

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

Upvotes: 0

Related Questions