Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

range query

AlfioSep 3 2008 — edited Nov 2 2010
I have a table:

CREATE TABLE TEST
(
name VARCHAR2(10 BYTE),
num NUMBER
)

select * from test order by num;
name, num
A, 1 *
B, 4
C, 8
D, 18 *
h, 21
E, 34 *
f, 35
t, 36
L, 51 *
Z, 55

I need a query to get records where the num value >= previous got num value + 15
(the records to find are marked with *)
The first record is always valid.
In the example above, I don't get record (C, 8) because the difference with num value of record (A,1) is less then 15,
but i get record (L, 51) because the difference with num value of record (E,34) is more then 15.

thanks
This post has been answered by 450441 on Sep 3 2008
Jump to Answer

Comments

Frank Kulash
Answer
Hi, Peter,

I'm not sure I understand what you're trying to do.
It might help to know
(1) What is your concept of "group" in this problem? In what sense do subcodes 5, 6 and 8 form a group? Is it that they share the quality of not overlapping with some other row with the same product? That is, will there be at most two groups per product: rows that overlap with every other row, and rows that don't?
(2) What you mean by "mutually disclosing"? Is it that the min_val to max_val ranges do not overlap?

If you want to identify the subcodes for which there is another subcode with the same product but non-overlapping min_val and max_val, then: 
SELECT	*
FROM	t	m
WHERE	EXISTS (
	    	   SELECT  NULL
		   FROM	   t
		   WHERE   product =  m.product
		   AND	   (	   min_val > m.max_val
		   	   OR	   max_val < m.min_val
			   )
	       );
This assumes that min_val <= max_val on each row.

The rest of this message concerns the CONNECT BY query you posted, in case you want to understand it better.

When I run the CONNECT BY query you posted, I get these results: 
P    SUBCODE    MIN_VAL    MAX_VAL
- ---------- ---------- ----------
A          0          0         99
A          1          0         99
A          4          0         99
A          5         45         60
A          6         20         40
A          7          0         99
A          8         60         65
A          9          0         99
The START WITH clause includes all rows: you might as well not have a START WITH clause: 
 10  start with -- This, start with, seems "clumpsy"
 11            min_val in (  select min_val
 12                            from t
 13                        group by product, subcode, min_val)
Since you're GROUPing BY min_val, the sub-query will produce a list of all the min_vals in the table.

The CONNECT BY clause considers row x to be the parent of row y if y's min_val to max_val range is a subset of x's: 
 14  connect by nocycle -- This, nocycle, really bothers me!
 15                     min_val >= prior min_val
 16                 and max_val <= prior max_val
 17                 and product = prior product
 18                 and subcode <> prior subcode
That's a kind of overlapping, but much stricter.

Is there a particular reason why NOCYCLE bothers you here? Like DISTINCT, NOCYCLE is often the mark of something being done in a clumsy way, but not a sure indicator. In a way, they're like duct tape: most of the time when you see duct tape it's being used inappropriately, but that doesn't mean appropriate uses don't exist.

Since I'm not sure what you're trying to do, I'm not sure if the WHERE clause helps or not: 
  9       where connect_by_isleaf = 1 -- Why does this help me?
It definitely matters. Without the WHERE clause, the output includes all rows. With the WHERE clause, the rows with subcodes 2 and 3 are excluded. Why? Because they always have children. Subcode 2 (18-67) is the parent of 3 (20-65), and 3 is the parent of 5 (45-60), 6 (20-40) and 8 (60-65). (2 is also the parent of 5, 6 and 8, as well as their grandparent.)
You may ask: What about subcodes 0, 1, and 7: don't they always have children? Why do they pass the WHERE clause? 0, 1 and 7 (each ranging from 0 to 99) are the parents of all the other rows, including each other. Therefore, they will all be invloved in cycles, and whenever CONNECT_BY_ISCYCLE=1, CONNECT_BY_ISLEAF will also be 1, so while 0, 1 and 7 do have children, they do not always have children.

Edited by: Frank Kulash on May 19, 2009 1:07 PM
Marked as Answer by Peter Gjelstrup · Sep 27 2020
Peter Gjelstrup
Brillant as always, Frank. Ten points and mange tak for you.

(1) What is your concept of "group" in this problem? In what sense do subcodes 5, 6 and 8 form a group? Is it that they share the quality of not overlapping with some other row with the same product? That is, will there be at most two groups per product: rows that overlap with every other row, and rows that don't?
By group I mean, that when isolated from other subcodes, a group does not overlap. For product A, I have 10 different subcodes. In certain situations I'm allowed to consider 5,6 and 8 as one, since they don't overlap. In general the data presented results in 8 groups, 7 groups having 1 subcode, 1 group having 3 subcodes.
(2) What you mean by "mutually disclosing"? Is it that the min_val to max_val ranges do not overlap?
Yes. They are however allowed to be equal.

As to your query. Seems you're right, it might actually be that "simple". I changed slightly, allowing >= and <=
SQL> SELECT m.*,
  2         CASE
  3            WHEN
  4            EXISTS (
  5                     SELECT  NULL
  6                     FROM    t
  7                     WHERE   product = m.product
  8                     AND     (       min_val >= m.max_val
  9                             OR      max_val <= m.min_val
 10                             )
 11                   )
 12         THEN 1 END group_flag
 13    FROM t m;

PRODUCT    SUBCODE    MIN_VAL    MAX_VAL GROUP_FLAG
------- ---------- ---------- ---------- ----------
A                0          0         99
A                1          0         99
A                2         18         67
A                3         20         65
A                4          0         99
A                5         45         60          1
A                6         20         40          1
A                7          0         99
A                8         60         65          1
A                9          0         99

10 rows selected.

SQL>
This assumes that min_val <= max_val on each row.
Your assumption is perfectly correct, as expessed in check constraint on table t.

>
The rest of this message concerns the CONNECT BY query you posted, in case you want to understand it better.

When I run the CONNECT BY query you posted, I get these results: 
P    SUBCODE    MIN_VAL    MAX_VAL
- ---------- ---------- ----------
A          0          0         99
A          1          0         99
A          4          0         99
A          5         45         60
A          6         20         40
A          7          0         99
A          8         60         65
A          9          0         99
Ouch, my bad. Somehow I posted the wrong query. I have edited line 15 from 
  15                    min_val >= prior min_val
into 
  15                    min_val > prior min_val
The START WITH clause includes all rows: you might as well not have a START WITH clause: 
10  start with -- This, start with, seems "clumpsy"
11            min_val in (  select min_val
12                            from t
13                        group by product, subcode, min_val)
That's great, somehow I was misled to believe that wew should always have a START WITH.

Thank you for your remainding comments on hierarchial query. I'll definitely have to play around with it to fully understand. With the edit mentioned, and
without a START WITH it now looks as,
SQL>  select distinct -- This, distinct, bothers me!
  2                       product
  3                      ,subcode
  4                      ,min_val
  5                      ,max_val
  6         from t
  7        where connect_by_isleaf = 1 -- Why does this help me?
  8   connect by nocycle -- This, nocycle, really bothers me!
  9                      min_val > prior min_val
 10                  and max_val <= prior max_val
 11                  and product = prior product
 12                  and subcode <> prior subcode  -- Not necessary
 13     order by product
 14             ,subcode
 15             ,min_val
 16             ,max_val;

PRODUCT    SUBCODE    MIN_VAL    MAX_VAL
------- ---------- ---------- ----------
A                5         45         60
A                6         20         40
A                8         60         65

3 rows selected.

SQL>
One final question. In your solution with EXISTS, you use < and >. In my rewritten example I use <= and >=. It seems that it still works, even without a condition on subcode != m.subcode in the subquery.
Maybe I'm too tired by now, but why is that? - just don't get it

Regards
Peter
Solomon Yakobson
Just for fun - MODEL solution:
select  product,
        subcode,
        min_val,
        max_val
  from  (
         select  *
           from  t
           model
           partition by(
                        product
                       )
           dimension by(
                        subcode,
                        min_val,
                        max_val
                       )
         measures(
                  0 cross_points
                 )
         rules(
               cross_points[any,any,any] order by subcode = count(*)[any,min_val >= cv(max_val),any] +
                                                            count(*)[any,any,max_val <= cv(min_val)]
              )
        )
  where cross_points > 0
  order by min_val
/

P    SUBCODE    MIN_VAL    MAX_VAL
- ---------- ---------- ----------
A          6         20         40
A          5         45         60
A          8         60         65

SQL>
SY.
1 person found this helpful
Frank Kulash
Hi, Peter,

You're welcome. (Is that "Velbekomme" or "Vær så venlig"?)
At any rate, I'm glad to help.
Peter Gjelstrup wrote:

One final question. In your solution with EXISTS, there is no condition on subcode != m.subcode in the subquery.
Why is that?
It's easier to discuss if both table have aliases:
I said the equivalent of: 
WHERE	EXISTS (
	    	   SELECT  NULL
		   FROM	   t	s
		   WHERE   s.product =  m.product
--		   AND	   s.subcode != m.subcode
		   AND	   (	   s.min_val > m.max_val
		   	   OR	   s.max_val < m.min_val
			   )
	       );
Since subcode is unique, and min_val < max_val on every row, 
...		   	   (	   s.min_val > m.max_val
		   	   OR	   s.max_val < m.min_val
			   )
implies 
...		   	   s.subcode != m.subcode
that is, if the ranges don't overlap, then s and m must be different.
Or, looking at it from the other direction, if s and m do refer to the same row, then both of the sub-conditions in 
...		   	   (	   s.min_val > m.max_val
		   	   OR	   s.max_val < m.min_val
			   )
will be FALSE, so there's no need to test for a different subcode.

I have an additional question, too.
I was assuming that two rows could overlap without one being inside the other, that is, I was allowing data such as: 
insert into t (product, subcode, min_val, max_val) values ('B',	1, 0,  99);
insert into t (product, subcode, min_val, max_val) values ('B', 2, 18, 67);
insert into t (product, subcode, min_val, max_val) values ('B', 3, 60, 80);
insert into t (product, subcode, min_val, max_val) values ('B', 4, 70, 99);
where B3 overlaps with both B2 and B4, but B2 and B4 do not overlap with each other.
Is this possible in your application?
1 person found this helpful
Solomon Yakobson
Peter Gjelstrup wrote:

One final question. In your solution with EXISTS, you use < and >. In my rewritten example I use <= and >=. It seems that it still works, even without a condition on subcode != m.subcode in the subquery.
Frank's solution assumes "touching" as overlap. The reason it works same for both < and > and <= and >= is because there is a third non-touching row. If you exclude it:
SQL> WITH t1 as (select * from t where subcode != 6)
  2  SELECT *
  3  FROM t1 m
  4  WHERE EXISTS (
  5           SELECT  NULL
  6       FROM    t1
  7       WHERE   product =  m.product
  8       AND    (    min_val > m.max_val
  9           OR    max_val < m.min_val
 10        )
 11          )
 12  /

no rows selected

SQL> WITH t1 as (select * from t where subcode != 6)
  2  SELECT *
  3  FROM t1 m
  4  WHERE EXISTS (
  5           SELECT  NULL
  6       FROM    t1
  7       WHERE   product =  m.product
  8       AND    (    min_val >= m.max_val
  9           OR    max_val <= m.min_val
 10        )
 11          )
 12  /

P    SUBCODE    MIN_VAL    MAX_VAL
- ---------- ---------- ----------
A          5         45         60
A          8         60         65

SQL>
SY.
1 person found this helpful
Solomon Yakobson
BTW, what results do you expect if we would change min_val to 50 for subcode 8:
SQL> select * from t1
  2  /

P    SUBCODE    MIN_VAL    MAX_VAL
- ---------- ---------- ----------
A          0          0         99
A          1          0         99
A          2         18         67
A          3         20         65
A          4          0         99
A          5         45         60
A          6         20         40
A          7          0         99
A          8         50         65
A          9          0         99

10 rows selected.

SQL>
Now we have 6 and 5 do not overlap, 6 and 8 do not overlap, however 5 and 8 do overlap. Do we have two non-overlapping groups 6,5 and 6,8 or none?

SY.
1 person found this helpful
Peter Gjelstrup
Hi SY,

That's great, somehow I knew it was comming. All that's missing now is an xml solution of Michael's :-)

MODEL, definitely something to learn about for me.

Regards
Peter
Peter Gjelstrup
Godmorgen Frank,
(Is that "Velbekomme" or "Vær så venlig"?)
"Velbekomme" is absolutely correct, "Vær så venlig" is more like saying please when you request something to be done.

Since subcode is unique, and min_val < max_val on every row,
Yes of course. Initially I had
constraint t_val_chk check (min_val <= max_val)
and entered a record having MIN_VAL = MAX_VAL. That must have confused me still. Thanks.

I have an additional question, too.
I was assuming that two rows could overlap without one being inside the other, that is, I was allowing data such as:
Good catch, this is of mine points when discussion this with Business. In that case I would currently say that 2 and 4 could be grouped:
So far, Business says that this does not exist and should never happen (*sigh*), so grouping 2 and 4 should be correct (even if it did happen).
SQL>SELECT m.*,
  2         CASE
  3            WHEN
  4            EXISTS (
  5                     SELECT  NULL
  6                     FROM    t s
  7                     WHERE   s.product = m.product
  8                     AND     (       s.min_val >= m.max_val
  9                             OR      s.max_val <= m.min_val
 10                             )
 11                   )
 12         THEN 1 END group_flag
 13    FROM t m
 14   WHERE product = 'B';

PRODUCT    SUBCODE    MIN_VAL    MAX_VAL GROUP_FLAG
------- ---------- ---------- ---------- ----------
B                1          0         99
B                2         18         67          1
B                3         60         80
B                4         70         99          1

SQL>
Business also says that there can be only one group having more than one member. I do know for a fact that rare situtations like this exists:
insert into t (product, subcode, min_val, max_val) values ('C', 1, 18, 60);
insert into t (product, subcode, min_val, max_val) values ('C', 2, 18, 60);
insert into t (product, subcode, min_val, max_val) values ('C', 7, 60, 65);
insert into t (product, subcode, min_val, max_val) values ('C', 8, 60, 65);
They know, by human inspection that this should be grouped as 1,7, 2,8. However based on the current information this cannot happen automagically,
so currently they should not be grouped. (Not grouping them is not nearly as bad as grouping something that should not have been)

Unfortunately this will happen:
SQL>SELECT m.*,
  2         CASE
  3            WHEN
  4            EXISTS (
  5                     SELECT  NULL
  6                     FROM    t s
  7                     WHERE   s.product = m.product
  8                     AND     (       s.min_val >= m.max_val
  9                             OR      s.max_val <= m.min_val
 10                             )
 11                   )
 12         THEN 1 END group_flag
 13    FROM t m
 14   WHERE product = 'C';

PRODUCT    SUBCODE    MIN_VAL    MAX_VAL GROUP_FLAG
------- ---------- ---------- ---------- ----------
C                1         18         60          1
C                2         18         60          1
C                7         60         65          1
C                8         60         65          1

SQL>
This best thing I can do right now, is to ensure that there are at most three subcodes in a group (Which is true according to business)) when I pick it up for processing.


Thanks again
Peter
Peter Gjelstrup
Frank's solution assumes "touching" as overlap. The reason it works same for both < and > and <= and >= is because there is a third non-touching row. If you exclude it:
Yes you are right. Thank you for a very fine explanation.

Regards
Peter
Peter Gjelstrup
Hi SY,
BTW, what results do you expect if we would change min_val to 50 for subcode 8:
This is really, really good. You just blew me out of the water. In that case I would have to discuss with Business how to group, if at all. The safe thing would be not to group.
Based on what I do know of the business domain, there should probably be a rule saying "If a subcode can be grouped in more than way it should not be grouped".
Unfortunately,
SQL>SELECT m.*,
  2         CASE
  3            WHEN
  4            EXISTS (
  5                     SELECT  NULL
  6                     FROM    t s
  7                     WHERE   s.product = m.product
  8                     AND     (       s.min_val >= m.max_val
  9                             OR      s.max_val <= m.min_val
 10                             )
 11                   )
 12         THEN 1 END group_flag
 13    FROM t m
 14   WHERE product = 'A';

PRODUCT    SUBCODE    MIN_VAL    MAX_VAL GROUP_FLAG
------- ---------- ---------- ---------- ----------
A                0          0         99
A                1          0         99
A                2         18         67
A                3         20         65
A                4          0         99
A                5         45         60          1
A                6         20         40          1
A                7          0         99
A                8         50         65          1
A                9          0         99

10 rows selected.

SQL>
None of the suggested solutions seems to handle this. So, thank you for catching this. Back to the drawing board I guess.

Regards
Peter
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 2 2008
Added on Sep 3 2008
9 comments
7,616 views