Home » SQL & PL/SQL » SQL & PL/SQL » Overlap Issue
Overlap Issue [message #278813] Mon, 05 November 2007 14:57 Go to next message
pria79
Messages: 8
Registered: June 2006
Location: Chicago US
Junior Member
Hi All,

The problem goes like this:

I have ranges in a table with start and end point of the range in 2 different columns. For eg:

Table Rnge

Rng1 Rng2
100 103
104 105
106 108
107 107
108 108

My problem is overlapping ranges, which are not allowed. I want to find them all from the table. For instance, from above example i have

Rng1 Rng2
106 108
107 107
108 108

However only one value (the top one, 106-108) is valid, I need to keep just the top value and delete the next two values.

How can i achieve this? Will it be possible with a single SQL

Thanks a lot in advance
Shweta
Re: Overlap Issue [message #278815 is a reply to message #278813] Mon, 05 November 2007 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why is this 106-108 that is one valid?
What in the case you have 106-106, 106-107, 107-107, 107-108?
And if now we add 106-108 and 105-107 or 107-109?

Regards
Michel
Re: Overlap Issue [message #278846 is a reply to message #278813] Mon, 05 November 2007 23:32 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Is this what you want to achieve ?


select   RNG1,RNG2   from orafaq_range   start with   RNG1=100  connect by  prior rng2+1= rng1


Delete from orafaq_range where (RNG1,RNG2) not in (
select   RNG1,RNG2   from orafaq_range   start with   RNG1=100  connect by  prior rng2+1= rng1)


Thumbs Up
Rajuvan
Re: Overlap Issue [message #278860 is a reply to message #278846] Tue, 06 November 2007 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ how could you post a solution without knowing what OP wants, for instance in the cases I posted
2/ your "solution" will not work, I don't even see the logic behind this, who say that the range are/may be/have to be contiguous?

Regards
Michel
Re: Overlap Issue [message #278864 is a reply to message #278860] Tue, 06 November 2007 00:59 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


The Solution that I provided may not be specific to OP's requirement . That's what i asked the OP "Is this what you want to achieve ?" .What i wanted is to give OP a clue on which he might be able to *customize*.

The Solution is Specific to the data given in the Question. There it seems that ranges are contiguous . In all most all Overlapping condition will not obey this rule of contiguous range.

like ...

106 108
107 109

106 108
108 109

Anyway Ball is in OP's Court... Let him reply what he needs.

Thumbs Up
Rajuvan.
Re: Overlap Issue [message #278866 is a reply to message #278864] Tue, 06 November 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Anyway Ball is in OP's Court... Let him reply what he needs.

Yes and I'm waiting for him.

This is why I say: until he provides a complete specification any answer is wrong.
You could also answer:
select 106, 108 from dual
union all
select 107, 107 from dual
usions all
select 108, 108 from dual
/
as this also fit the result provided... but is this an answer for the problem?

Regards
Michel
Re: Overlap Issue [message #278869 is a reply to message #278866] Tue, 06 November 2007 01:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I disagree, Michel. You can pick on anyone, anytime but at least Rajuvan has put some effort into it. I read the question as follows:

- we have a table t1 with 2 numeric columns col1 and col2
- col1 should always be < col2
- the combination col1 - col2 represents a range
- ranges cannot overlap
- in case of an overlap, the range with the smallest col1 value should remain.

Your answer does not start from the initial data set given by the OP Wink.

MHE
Re: Overlap Issue [message #278871 is a reply to message #278813] Tue, 06 November 2007 01:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Thanks Maaher for your support against the BIG 'M' in the forum Smile

Thumbs Up
Re: Overlap Issue [message #278875 is a reply to message #278869] Tue, 06 November 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten,

I think a question worths an answer only when it is complete.
And you know I think learning someone how to post his question is more important than giving a "solution".

What if you have 105-107 and 106-108? With your algorithm you keep 105-107 and lose 106-108.

For my query you're right just replace "from dual" by "from mytable where rownum=1". Very Happy

Regards
Michel
Re: Overlap Issue [message #278877 is a reply to message #278871] Tue, 06 November 2007 01:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm not supporting against anyone, I just did not agree with Michel on this one.

And I believe that fair's fair. I can still remember how I started at OraFAQ. It's not always easy to provide all details at once. Certainly not when you're just beginning with Oracle.

MHE

Edit: addition to Michel's reply:
Michel Cadot wrote on Tue, 06 November 2007 08:28

I think a question worths an answer only when it is complete.
I know that that is your credo, but that is exactly where I disagree: for someone who is in the middle of learning SQL or PL/SQL, it is not very encouraging to read "smart" or wise crack answers. And yes, we can give such an answer to 90% of the forum questions but then we can close the forum all together* and just put a single link to the Oracle documentation (provided it does not exceed 80 characters Razz). Bear in mind we all started at some point.

Michel Cadot wrote on Tue, 06 November 2007 08:28

And you know I think learning someone how to post his question is more important than giving a "solution".
And here I agree with you. I try to explain how and why I come to a given answer.

Michel Cadot wrote on Tue, 06 November 2007 08:28

What if you have 105-107 and 106-108? With your algorithm you keep 105-107 and lose 106-108.
Exactly. That's one of the things I distilled from the question. I'd do it differently if it were my party, but alas...

*no we cannot: it is Frank Naude who runs this show Very Happy.

[Updated on: Tue, 06 November 2007 02:06]

Report message to a moderator

Re: Overlap Issue [message #278886 is a reply to message #278877] Tue, 06 November 2007 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok Maarten, point taken, I'll try to be less strict and direct in my answers (but still can't read more than 80 characters per line Shocked )

Regards
Michel
Re: Overlap Issue [message #279031 is a reply to message #278886] Tue, 06 November 2007 12:15 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
FYI, there are some great discussions & solutions on asktom.oracle.com on this topic.
Previous Topic: More stale connections
Next Topic: select date range values
Goto Forum:
  


Current Time: Thu Dec 08 16:37:41 CST 2016

Total time taken to generate the page: 0.07751 seconds