Thanks everyone for there replies... this has been very helpful and it's
much easier to do than I first thought :)
If anyone is interested I did find another approach that works (please
excuse the different table names, I'm using my own data set):
SELECT l.PER_RAW_NUMBER AS L_Bound, MIN(t.PER_RAW_NUMBER) AS U_Bound
FROM PERMISSION l
LEFT OUTER JOIN PERMISSION r ON r.PER_RAW_NUMBER = l.PER_RAW_NUMBER - 1
LEFT OUTER JOIN
(
SELECT a.PER_RAW_NUMBER
FROM PERMISSION a
LEFT OUTER JOIN PERMISSION b ON b.PER_RAW_NUMBER = a.PER_RAW_NUMBER
+ 1
WHERE b.PER_RAW_NUMBER IS NULL
)
t on t.PER_RAW_NUMBER >= l.PER_RAW_NUMBER
WHERE r.PER_RAW_NUMBER IS NULL
GROUP BY l.PER_RAW_NUMBER;
On Tue, Sep 8, 2009 at 12:40 PM, Michael Moore <michaeljmo...@gmail.com>wrote:
> There is a difference between David and Sonty's results if the data
> contains duplicate values. If duplicate values are not possible (could be
> assumed from the original question), then both queries seem to do the job.
>
> I get some interesting results with this sequence which contains
> duplicates,
> 1,3,4,9,9,11,12,12,13,21,22,24
>
> David's result is
> 1,1
> 3,4
> 9,9
> 9,9
> 11,12
> 12,13
> 21,22
> 24,24
>
> Sonty's result is
> 1,1
> 3,4
> 21,22
> 24,24
>
> Regardless, I think both solutions are ingenious.
> Mike
>
>
>
>
> On Tue, Sep 8, 2009 at 6:48 AM, ddf <orat...@msn.com> wrote:
>
>>
>>
>>
>> On Sep 7, 10:26 am, Michael Moore <michaeljmo...@gmail.com> wrote:
>> > Sonty, I get an error with this. I replace your table name with t so I
>> could
>> > use Benoit's data.
>> > Mike
>> >
>> > SELECT xph2.l low, xph.l high
>> > FROM t xph, t xph2
>> > WHERE NOT EXISTS (SELECT l
>> > FROM t
>> > WHERE l = xph.l + 1)
>> > AND NOT EXISTS (SELECT l
>> > FROM t
>> > WHERE l = xph2.l - 1)
>> > AND (xph.l - xph2.l) + 1 = (SELECT COUNT (1)
>> > FROM t
>> > WHERE l <= xph.l AND l >= xph2.l)
>> > ORDER BY 1, 2
>> > *
>> > Error at line 9
>> > ORA-00904: "XPH2"."L": invalid identifier
>> >
>> > Script Terminated on line 1.
>> >
>> >
>> ---------------------------------------------------------------------------------------------------
>> >
>> >
>> >
>> > On Mon, Sep 7, 2009 at 1:18 AM, sonty <saurabh.zen...@gmail.com> wrote:
>> >
>> > > This is working for me:
>> >
>> > > SELECT xph2.l low,xph.l high
>> > > FROM test_temp xph, test_temp xph2
>> > > WHERE NOT EXISTS (SELECT l
>> > > FROM test_temp
>> > > WHERE l = xph.l + 1)
>> > > AND NOT EXISTS (SELECT l
>> > > FROM test_temp
>> > > WHERE l = xph2.l - 1)
>> > > AND (xph.l - xph2.l)+1 =
>> > > (SELECT COUNT (1)
>> > > FROM test_temp
>> > > WHERE l <= xph.l
>> > > AND l >= xph2.l)
>> > > order BY 1,2
>> >
>> > > where table test_temp has column l.
>> >
>> > > Regards,
>> > > Sonty
>> >
>> > > On Sep 6, 10:25 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
>> > > > In oracle you could create a "table function" which you use like
>> this.
>> >
>> > > > select * from TABLE(mytab);
>> >
>> > > > where mytab is the name of a pl/sql function. That function must be
>> in a
>> > > > pl/sql package. It can not be 'stand alone'.
>> >
>> > > > A google search on ORACLE,PLSQL,TABLE,FUNCTION should get you
>> everything
>> > > you
>> > > > need to know. If not, write back.
>> >
>> > > > On Sun, Sep 6, 2009 at 5:43 AM, David Hart <
>> david.john.h...@gmail.com
>> > > >wrote:
>> >
>> > > > > I would expect this:LBound UBound
>> > > > > 1 2
>> > > > > 5 5
>> > > > > 7 8
>> > > > > 12 12
>> >
>> > > > > I'm more from a SQL Server background... could I create a
>> temporary
>> > > table,
>> > > > > fill it with the correct values and then return the result from a
>> SPROC
>> > > or
>> > > > > function?
>> >
>> > > > > D.
>> >
>> > > > > On Sat, Sep 5, 2009 at 10:47 AM, Michael Moore <
>> > > michaeljmo...@gmail.com>wrote:
>> >
>> > > > >> This would probably best be solved procedurally. If you can come
>> up
>> > > with a
>> > > > >> query that does it, it will probably be very ugly. Also, what
>> result
>> > > would
>> > > > >> you expect in this case:
>> >
>> > > > >> 1
>> > > > >> 2
>> > > > >> 5
>> > > > >> 7
>> > > > >> 8
>> > > > >> 12
>> >
>> > > > >> ??
>> >
>> > > > >> Mike
>> >
>> > > > >> On Sat, Sep 5, 2009 at 6:49 AM, Dave Hart <
>> david.john.h...@gmail.com
>> > > >wrote:
>> >
>> > > > >>> Hi,
>> > > > >>> I have a table which includes data like this:
>> >
>> > > > >>> SEQ
>> > > > >>> 1
>> > > > >>> 2
>> > > > >>> 3
>> > > > >>> 4
>> > > > >>> 8
>> > > > >>> 9
>> > > > >>> 10
>> > > > >>> 11
>> > > > >>> 12
>> > > > >>> 13
>> > > > >>> 20
>> > > > >>> 21
>> > > > >>> 22
>> > > > >>> 23
>> > > > >>> 24
>> >
>> > > > >>> I want to be able to write a query that displays the upper and
>> lower
>> > > > >>> bounds of each range:
>> > > > >>> LBound UBound
>> > > > >>> 1 4
>> > > > >>> 8 13
>> > > > >>> 20 24
>> >
>> > > > >>> I've found plenty of queries that find the start and end of the
>> gaps
>> > > > >>> but none that display the start and end of the ranges.
>> >
>> > > > >>> Any help is appreciated.
>> >
>> > > > >>> Thanks,
>> > > > >>> Dave- Hide quoted text -
>> >
>> > > > - Show quoted text -- Hide quoted text -
>> >
>> > - Show quoted text -
>>
>> You cannot do that without creating table 't' with a 'proper'
>> definition (one that matches sonty's example table). This works:
>>
>> SQL> create table t(l number not null);
>>
>> Table created.
>>
>> SQL>
>> SQL> insert into t values (1 );
>>
>> 1 row created.
>>
>> SQL> insert into t values (2 );
>>
>> 1 row created.
>>
>> SQL> insert into t values (3 );
>>
>> 1 row created.
>>
>> SQL> insert into t values (4 );
>>
>> 1 row created.
>>
>> SQL> insert into t values (8 );
>>
>> 1 row created.
>>
>> SQL> insert into t values (9 );
>>
>> 1 row created.
>>
>> SQL> insert into t values (10);
>>
>> 1 row created.
>>
>> SQL> insert into t values (11);
>>
>> 1 row created.
>>
>> SQL> insert into t values (12);
>>
>> 1 row created.
>>
>> SQL> insert into t values (13);
>>
>> 1 row created.
>>
>> SQL> insert into t values (20);
>>
>> 1 row created.
>>
>> SQL> insert into t values (21);
>>
>> 1 row created.
>>
>> SQL> insert into t values (22);
>>
>> 1 row created.
>>
>> SQL> insert into t values (23);
>>
>> 1 row created.
>>
>> SQL> insert into t values (24);
>>
>> 1 row created.
>>
>> SQL> commit;
>>
>> Commit complete.
>>
>> SQL>
>> SQL> SELECT xph2.l low, xph.l high
>> 2 FROM t xph, t xph2
>> 3 WHERE NOT EXISTS (SELECT l
>> 4 FROM t
>> 5 WHERE l = xph.l + 1)
>> 6 AND NOT EXISTS (SELECT l
>> 7 FROM t
>> 8 WHERE l = xph2.l - 1)
>> 9 AND (xph.l - xph2.l) + 1 = (SELECT COUNT (1)
>> 10 FROM t
>> 11 WHERE l <= xph.l AND l >= xph2.l)
>> 12 ORDER BY 1, 2;
>>
>> LOW HIGH
>> ---------- ----------
>> 1 4
>> 8 13
>> 20 24
>>
>> SQL>
>>
>>
>> David Fitzjarrell
>>
>>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
