CODEWITHSUNDEEP
sqloracle-database
Ganesh Hegde
Ganesh Hegde

Reputation: 227

How to fetch range based on number in oracle sql?

I have table with two columns. One is start and another column is end. If I give any number it has to pick all matching rows in the table. Example:

start |  End
100   |  200
100   |  500
1     |  345
200   |  400

If i give 123 it should pick:

100 | 200
100 | 500
1   | 345

How to write query for this?

Upvotes: 0

Views: 93

Answers (2)

neshkeev
neshkeev

Reputation: 6476

I don't understand your confusion with this task:

with t(strt, En) as (
  select 100,  200 from dual union all
  select 100,  500 from dual union all
  select 1,  345 from dual union all
  select 200,  400 from dual
)
select *
  from t
 where 123 between strt and en

STRT     EN
-----------
 100    200
 100    500
   1    345

Upvotes: 2

Bulat
Bulat

Reputation: 6969

Inclusive select:

SELECT * FROM table
WHERE @value BETWEEN start AND end

Exclusive select:

SELECT * FROM table
WHERE @value > start AND value < end

Upvotes: 2

Related Questions