Check if Integer is between a range of column values

Question

Consider the following table schema:

----------------------------------
|  ID  |  MinValue  |  MaxValue  |
----------------------------------
|  1   |      0     |    10      |
|  2   |     11     |    20      |
|  3   |     21     |    30      |

I want to be able to pass an integer, and have it return the appropriate ID where that value matches the range between Min and Max Value.

EG:

Input = 17
Output = 2

Input = 4
Output = 1

Input = 26
Output = 3

I thought I could do something like:

SELECT ID FROM MyTable WHERE MinValue >= @input AND MaxValue <= @input

But it doesn't work, nothing is returned.

I'm sure the solution is blatantly simple, but i'm stumped.

What's the best way to achieve this in SQL Server?

Answer 1

try this

SELECT ID FROM MyTable WHERE @input BETWEEN MinValue AND MaxValue

DESCRIPTION of BEETWEEN

The SQL BETWEEN Condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL BETWEEN Condition is:

expression BETWEEN value1 AND value2; Parameters or Arguments

expression is a column or calculation.

value1 and value2 create an inclusive range that expression is compared to.

NOTE

The SQL BETWEEN Condition will return the records where expression is within the range of value1 and value2 (inclusive).

ref: http://www.techonthenet.com/sql/between.php

or you can also use like

MinValue <= @input AND MaxValue >= @input

Answer 2

Try this,

SELECT ID FROM MyTable WHERE @input BETWEEN MinValue AND MaxValue.

Or flip the equality signs in your statement.

SELECT ID FROM MyTable WHERE MinValue <= @input AND MaxValue >= @input