Select one row from a range of rows in SQL Server

Question

Hi all,

I've come across a very interesting problem.. quite lost actually. Suppose I have a table with range of values, like:

id.........From........To
1..........0...........5
2..........6...........10
3..........11...........15
4..........16...........29

So now I have an integer say @MyInt = 8, I want to select that single row in which this integer lies in. In this example, 8 comes between 6 and 10 so it should select row id=2

I obviously cant do

select * from table where From < @MyInt AND To >= @MyInt

because it will select all rows, since all the numbers will be lesser and/or greater..

So how do I select that single row in a situation like this?

Many Thanks!

Answer 1

Using:

SELECT t.*
  FROM TABLE t
 WHERE @MyInt BETWEEN t.[from] AND t.to 

...works because both criteria on a per row basis need to be satisfied to return the row - it's equivalent to:

SELECT t.*
  FROM TABLE t
 WHERE @MyInt <= t.to 
   AND @MyInt >= t.[from]

...which is fine, because the values are exclusive sets. BETWEEN is ANSI standard, and always inclusive - Oracle, SQL Server, MySQL, PostgreSQL, SQLite will all return the same data (assuming similar data type).

If you used:

AND @MyInt > t.[from]

...you'd never capture rows where your variable is identical to the from value (IE: 0, 6, 11, 16).

Answer 2

If you are using MSSQL you might want to check out the the BETWEEN operator, which simplifies these kind of x < value < y situations.

Answer 3

The statement you suggest should work fine:

select * from table where From < @MyInt AND To >= @MyInt  

I think you are confusing AND with UNION:

select * from table where From < @MyInt
UNION
select * from table where To >= @MyInt  

will give you all the rows, however, your statement will give you only the rows (row) that satisfies both conditions.

(Actually, it will miss the case where @MyInt equals the lower bound. It should be:

select * from table where From <= @MyInt AND  @MyInt <= To 

I also reverse a condition, so the relationship is more obvious)