sql - query between 2 rows

Have question. I'm doing a select where I need to grab 2 rows. I have a value of 13000.00000. I need to grab both rows 2 and 3 since it falls between the 10000 (min range) and 15000 (min range)

This statement just pulls in row 2.

select *
from TABLE1
where 13000 between TABLE1.MIN_RANGE and TABLE1.MAX_RANGE;

TABLE1

Row min_range   max_range   return_value
1   0.00000      9999.99900 1.15
2   10000.00000 14999.99900 1.25
3   15000.00000 19999.99900 1.35
4   20000.00000 2499开发者_Python百科9.99900 1.14

---

You want to get the first row that falls below the input and the first row that falls above the input, both using MIN_RANGE as the descriminator:

select top 1 *
from TABLE1
where TABLE1.MIN_RANGE < @input
order by MIN_RANGE desc
UNION
select top 1 *
from TABLE1
where TABLE1.MIN_RANGE >= @input
order by MIN_RANGE;

This feels like a solution for a window function, which maybe someone can post.

---

Your output is correct, 13000 does not fall in the range of row 3.

---

The following SQL query should work for you:

DECLARE @TestRange NUMERIC(18,0)
SET @TestRange = 13000
SELECT *, (max_range - min_range) 
    FROM TABLE1 
    WHERE ((@TestRange >= min_range AND @TestRange <= max_range) 
       OR ((@TestRange >= min_range -  (max_range - min_range) 
       AND @TestRange <= max_range))) 
    ORDER BY min_range

Tested with Microsoft SQL Server. Change the 13000 to whatever value you need to test against, or feel free to hard code the value