Very impressive! I will definitly try this. Thanks Again, Jake
On Tue, Oct 21, 2003 at 06:44:25PM -0800, Larry Elkins wrote: > Stephane, > > Pretty slick trick!!! But I can't believe that you, of all people, didn't > throw in an analytic just to confuse things even more, plus, avoid that > second pass on sliced_kipling ;-) > > SQL> l > 1 select translate(ltrim(text, '/'), '/', ' ') verse > 2 from (select text, row_number() over (partition by verse order by > verse, lvl desc) rn > 3 from (select verse, level lvl, sys_connect_by_path(chunk, '/') > text > 4 from sliced_kipling > 5 connect by verse = prior verse > 6 and piece - 1 = prior piece)) > 7* where rn = 1 > SQL> / > > VERSE > ---------------------------------------------------------------------------- > ------------------------ > Oh, East is East, and West is West, and never the twain shall meet, > Till Earth and Sky stand presently at God's great Judgment Seat; > But there is neither East nor West, Border, nor Breed, nor Birth, > When two strong men stand face to face, tho' they come from the ends of the > earth! > > And hey, it reduced sorts and consistent gets in this particular case ;-) > Ok, my head hurts from dumb SQL tricks, someone else take it further from > here ;-) > > Later, > > Larry G. Elkins > [EMAIL PROTECTED] > > > SQL> select * from sliced_kipling; > > > > VERSE PIECE CHUNK > > ---------- ---------- -------------------------------------------------- > > 1 1 Oh, East is East, > > 1 2 and West is West, > > 1 3 and never the twain shall meet, > > 2 1 Till Earth and Sky stand > > 2 2 presently at God's great Judgment Seat; > > 3 1 But there is neither East nor West, > > 3 2 Border, > > 3 3 nor Breed, > > 3 4 nor Birth, > > 4 1 When two strong men stand face to face, > > 4 2 tho' they come from the ends of the earth! > > > > 11 rows selected. > > > > SQL> @magic_query > > > > VERSE > > ------------------------------------------------------------------ > > -------------- > > Oh, East is East, and West is West, and never the twain shall meet, > > Till Earth and Sky stand presently at God's great Judgment Seat; > > But there is neither East nor West, Border, nor Breed, nor Birth, > > When two strong men stand face to face, tho' they come from the ends of > > the earth! > > > > > > SQL> l > > 1 select translate(ltrim(x.text, '/'), '/', ' ') verse > > 2 from (select verse, level lvl, sys_connect_by_path(chunk, '/') text > > 3 from sliced_kipling > > 4 connect by verse = prior verse > > 5 and piece - 1 = prior piece) x, > > 6 (select verse, max(piece) piecemax > > 7 from sliced_kipling > > 8 group by verse) y > > 9 where x.verse = y.verse > > 10 and x.lvl = y.piecemax > > 11* order by x.verse > > SQL> > > > > I am not sure though that I satisfy the 'simple SQL' requirement :-). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Larry Elkins > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Thanks, Jake Johnson [EMAIL PROTECTED] ______________________________________________________________________ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Tires, and Wheel Packages. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jake Johnson INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
