Select one row from a range of rows in SQL Server
I've come across a very interes开发者_JS百科ting 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!
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).
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)
精彩评论