CODEWITHSUNDEEP
sqlmysql
user339108
user339108

Reputation: 13101

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

tableA contains {id, fromPos not null, toPos}

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)

Upvotes: 0

Views: 342

Answers (5)

Jonathan Leffler
Jonathan Leffler

Reputation: 753765

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);

Upvotes: 2

Kirill Leontev
Kirill Leontev

Reputation: 10931

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)

Upvotes: 3

StuartLC
StuartLC

Reputation: 107247

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)

Upvotes: 2

Mark Baker
Mark Baker

Reputation: 212412

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

Upvotes: 0

Anil Soman
Anil Soman

Reputation: 2467

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)

Upvotes: 0

Related Questions