AGRAWSAU AGRAWSAU - 1 year ago 60
SQL Question

Oracle: need a query to fetch data for certain range

data table

| id start_range end_range descr |
| 1 500 550 data1 |
| 2 500 500 data2 |
| 3 510 510 data3 |

So i need help with making a query that will input a range value and will check for start and end range, if it lies in the range then should return row accordingly.

For ex.

input: 500 output: data2
input: 510 output: data3
input: 502 output: data1
input: 550 output: data1
input: 551 output: null

Answer Source

You can get

  • one match for a number as 510 in 500-550
  • several matches as 500 both in 500-500 and 500-550
  • no match as for 551

So you'd have to pick the record you consider "best" from your matches. One way to do this is ranking your matches with ROW_NUMBER:

select descr 
  select descr,
    row_number() over (order by end_range - start_range) as rn
  from mytable
  where @value between start_range and end_range
where rn = 1;

This gives you a row with the "best" description, or no row when there is no match.

Another option is Oracle's KEEP DENSE_RANK:

select max(descr) keep (dense_rank first order by end_range - start_range)
from mytable
where @value between start_range and end_range;

This gives you the "best" description, or NULL when there is no match.