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 -~----------~----~----~----~------~----~------~--~---
