asked on

ORACLE 11G - Query to retrieve between a returned range.

table data look like the following:

RNK_ID RNK RNK_RANGE
1      E1      1
2      E2      2
3      E3      3
4      E4      4
5      E5      5
6      E6      6
7      E7      7
8      E8      8
9      E9      9
10      W1      10
11      W2      11
12      W3      12
13      W4      13
14      W5      14
15      O1      15
16      O2      16
17      O3      17
18      O4      18
19      O5      20
20      O6      21
21      O9      22

The values 'E4' and 'O3' values will be passed from the main query to the following sub query for example:
SELECT RNK_RANGE FROM
RANK_LOOKUP LK
where
LK.RNK = 'E4' OR LK.RNK= 'O3'

From the dataset abover, the results returned will be 4 and 17.

I want to modify the query to also return the values between RNK_RANGE 4 AND 17.

slightwv (䄆 Netminder)

Is this not really the same as your previous question?

What is your RNK_ID or RNK column based on?

I suggest you either keep going with this question and close the other out or delete this one and continue with the other one.

HainKurt

try this:

select *
from RANK_LOOKUP a inner join
(
select min(RNK_RANGE) r1, max(RNK_RANGE) r2 from RANK_LOOKUP where RNK in ('E4','O3')
) b on a.rnk_range between r1 and r2

HainKurt

or this:

select *
from RANK_LOOKUP
where rnk_range between
(select min(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
and
(select max(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))

cookiejar

ASKER

How would I construct the in ('E4','O3') when I am passing the column name min_rank for ‘E4’ and the column name max_rank for ‘O3’? Would it look like ‘(‘ || min_rank || , || max_rank || ‘)’ I am not savvy with constructing the comma delimiter and single quote correct syntax.

HainKurt

like this:

RNK in (min_rank, max_rank)

no need for '

cookiejar

ASKER

In addition, to use this statement to retrieve ranks that are not within this range would I change the in ('E4','O3') to not in ('E4','O3')

from RANK_LOOKUP
where rnk_range between
(select min(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
and
(select max(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))

HainKurt

use not between...

from RANK_LOOKUP
where rnk_range NOT between
(select min(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
and
(select max(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial