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).
