How to fetch range based on number in oracle sql?

Question

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?

I have removed the MySQL tag, as you state this is an Oracle question. — Arth
– Arth, Commented Sep 1, 2014 at 10:23

Bulat · Accepted Answer · 2014-09-01 10:24:55Z

2

Inclusive select:

SELECT * FROM table
WHERE @value BETWEEN start AND end

Exclusive select:

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

answered Sep 1, 2014 at 10:24

Bulat

6,9992 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ganesh Hegde Over a year ago

But I dont have start and end. I have only number.

Bulat Over a year ago

How should I understand this: "I have table with two columns. One is start and another column is end."

neshkeev · Accepted Answer · 2014-09-01 11:58:52Z

2

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

answered Sep 1, 2014 at 11:58

neshkeev

6,4863 gold badges32 silver badges49 bronze badges

Collectives™ on Stack Overflow

How to fetch range based on number in oracle sql?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related