How to write a sql query to figure out if a value falls within a range

Question

tableA contains {id, fromPos not null, toPos}

• fromPos and toPos denotes a range of values for a particular row
• toPos is a nullable field

and has the following values

tableA (1, 5)     // means any position greater than or equal to 5
tableA (2, 5, 10) // means any position between 5 and 10 (inclusive)
tableA (3, 6) 
tableA (4, 7, 9)

• How to fetch all entries whose position is 7. It should return the id's (1,2,3,4)
• How to fetch all entries whose position is 5. It should return the id's (1,2)
• How to fetch all entries whose position is 8. It should return the id's (1,2,3,4)

Answer 1

Assuming that when there is no toPos mentioned in your example, it will be null.

1. Select * from tableA where fromPos <= 7 and (toPos=null or toPos >= 7)
2. Select * from tableA where fromPos <= 5 and (toPos=null or toPos >= 5)
3. Select * from tableA where fromPos <= 8 and (toPos=null or toPos >= 8)

Answer 2

SELECT id
  FROM <table>
 WHERE <value> BETWEEN fromPos AND COALESCE(toPos,99999999)

Answer 3

For a given target value, X, the query appears to be:

SELECT id
  FROM TableA
 WHERE fromPos <= X
   AND (toPos >= X OR toPos IS NULL);

Answer 4

select * 
  from tableA t
 where t.fromPos <= requested_position
   and coalesce(t.toPos, requested_position) >= requested_position

Coalesce means that requested_position will be put in comparison if t.toPos appears to be null, thus, a comparison will always yield true and you'll process only t.fromPos <= requested_position

Or, you may use between for better readability, which is the same:

select * 
  from tableA t
 where requested_position between t.fromPos and coalesce(t.toPos, requested_position)

Answer 5

declare @position int
set @position  = 8 

select id from tablea
where @position >= fromPos
and (@position <= toPos or toPos is null)


create table tableA
(
id int not null,
fromPos int not null,
toPos int null
)

insert into dbo.tableA(id, fromPos) values (1, 5)
insert into dbo.tableA(id, fromPos, toPos) values (2, 5, 10)
insert into dbo.tableA(id, fromPos) values (3, 6)  
insert into dbo.tableA(id, fromPos, toPos) values (4, 7, 9)