Encyclopedia Spine

With apologies to Donald J. Sobol.

Quiz time again.

This one comes from trying to create a directory into a result set using tabs or links, and mimicking what you see on a set of encyclopedias.  You know, A-B, C-D, etc.  But what I want to do is give a bit more information about what’s in each book, and mimic what usually happens with the letter S (Sa-Sm and Sn-Sz).

So, create a table like this:

create table ac1 as select distinct column_name from all_tab_columns;

And write a SQL statement to list a set of 15 book spines, each spanning the same number of columns and each spine displaying the most characters up until there is a difference between the spines.

For example, if the first book goes from A to BITS_SET and the next book starts with BLEVEL, the first book spine should say A… thru BI… and the second book spine should say BL… thru (whatever).

I have a solution, but I’m curious to see what you all come up with. 

As always, NO PL/SQL functions allowed (hey, that’s too easy).  You can use any version of Oracle you like — 8i, 9i, 10g, 11x, etc.

Here’s the output of my solution from my little XE database:

 

SPINE

SN

EN

A… thru BI…

A

BITS_SET

BL… thru COMMENT$…

BLEVEL

COMMENT$

COMMENT$T… thru DEFAULT#…

COMMENT$TEXT

DEFAULT#

DEFAULT$… thru ENDPOINT_N…

DEFAULT$

ENDPOINT_NUMBER

ENDPOINT_V… thru FRESHNESS_D…

ENDPOINT_VALUE

FRESHNESS_DATE

FRESHNESS_S… thru INUSE…

FRESHNESS_SCN

INUSE_FEATURES

INUSR… thru LOB_P…

INUSR

LOB_PROPERTY

LOB_R… thru MVL_OLDEST_PK…

LOB_READS

MVL_OLDEST_PK

MVL_OLDEST_PK_… thru OS…

MVL_OLDEST_PK_TIME

OS_USER_NAME

OT… thru PRECC…

OTHER

PRECCNT

PRECE… thru RESERVED…

PRECEDENCE

RESERVED

RESERVED1… thru SES…

RESERVED1

SES_ADDR

SET… thru SUBSTITUTION_STRING_02…

SETNUM

SUBSTITUTION_STRING_02

SUBSTITUTION_STRING_03… thru TRANSLATE_TO_I…

SUBSTITUTION_STRING_03

TRANSLATE_TO_ID

TRANSLATE_TO_L… thru Z

TRANSLATE_TO_LANG_CODE

ZIP

8 Responses to “Encyclopedia Spine”

  1. What happened to my day, Dominic? « OraStory Says:

    [...] Today, however, a poser from Dominic Delmolino also kicked things off. [...]

  2. Combining Hierarchical Queries With Analytics : Ardent Performance Computing Says:

    [...] Just a few days ago I was reading Dominic’s challenge to write a query that would give results like an Encyclopedia Spline. It apparently took over the entire day for another Dominic and I was planning to take it on myself too as soon as I had the time… but today another challenge came my way – but not just for entertainment, but for a real client. Basically I found myself looking at a 9000-word query (33 pages when I pasted it into MS Word). It was for a manufacturing client and it was pretty amazing… had been taking about 20-30 minutes but the guy added a single subquery and that plummeted the query to not even finishing in four hours. [...]

  3. Jun Feng Says:

    Dominic,
    I have my solution now. I am very curious your solution.
    Here is mine. I hope that is acceptable one.
    Jun

    select substr(beg, 1, instr(beg, ‘|’) -1 )||’… thru ‘
    ||substr(en, 1, instr(en, ‘|’) – 1)||’…’ spine,
    substr(beg, instr(beg, ‘|’)+1) sname,
    substr(en, instr(en, ‘|’)+1) ename from (
    select min(xx||’|’||column_name) beg, max(xx||’|’||column_name)en from (
    select substr(src.column_name,1, posi.p) xx, src.column_name, src.ne, src.bulk
    from (
    select column_name, min(n) p from ( — only get the smallest position for column
    select a.n, b.column_name
    from (select rownum n from dual connect by rownum

  4. ghassan Says:

    Dominic,
    here’s my try at it (I’ll not post my results, try it on your data) Very nice challenge indeed (but did not take a day)

    with a as (select column_name cn,ntile(15) over(order by column_name) ntile
    from ac1),
    arn as (select rownum rnn
    from dual
    connect by rownum

  5. Martin Preiss Says:

    it’s not pretty, it’s not generic – but it seems to work … (as long as you know the maximal length of your strings)

    SQL> r
    1 select case when lag_col is null then min_col
    2 when substr(min_col, 1, 1) substr(lag_col, 1, 1)
    3 then substr(min_col, 1, 1)
    4 when substr(min_col, 1, 1) = substr(lag_col, 1, 1)
    5 and substr(min_col, 1, 2) substr(lag_col, 1, 2) then substr(min_col, 1, 2)
    6 when substr(min_col, 1, 2) = substr(lag_col, 1, 2)
    7 and substr(min_col, 1, 3) substr(lag_col, 1, 3) then substr(min_col, 1, 3)
    8 when substr(min_col, 1, 3) = substr(lag_col, 1, 3)
    9 and substr(min_col, 1, 4) substr(lag_col, 1, 4) then substr(min_col, 1, 4)
    10 when substr(min_col, 1, 4) = substr(lag_col, 1, 4)
    11 and substr(min_col, 1, 5) substr(lag_col, 1, 5) then substr(min_col, 1, 5)
    12 when substr(min_col, 1, 5) = substr(lag_col, 1, 5)
    13 and substr(min_col, 1, 6) substr(lag_col, 1, 6) then substr(min_col, 1, 6)
    14 when substr(min_col, 1, 6) = substr(lag_col, 1, 6)
    15 and substr(min_col, 1, 7) substr(lag_col, 1, 7) then substr(min_col, 1, 7)
    16 when substr(min_col, 1, 7) = substr(lag_col, 1, 7)
    17 and substr(min_col, 1, 8) substr(lag_col, 1, 8) then substr(min_col, 1, 8)
    18 when substr(min_col, 1, 8) = substr(lag_col, 1, 8)
    19 and substr(min_col, 1, 9) substr(lag_col, 1, 9) then substr(min_col, 1, 9)
    20 when substr(min_col, 1, 9) = substr(lag_col, 1, 9)
    21 and substr(min_col, 1, 10) substr(lag_col, 1, 10) then substr(min_col, 1, 10)
    22 when substr(min_col, 1, 10) = substr(lag_col, 1, 10)
    23 and substr(min_col, 1, 11) substr(lag_col, 1, 11) then substr(min_col, 1, 11)
    24 when substr(min_col, 1, 11) = substr(lag_col, 1, 11)
    25 and substr(min_col, 1, 12) substr(lag_col, 1, 12) then substr(min_col, 1, 12)
    26 else null end
    27 || ‘ thru ‘ ||
    28 case when lead_col is null then max_col
    29 when substr(max_col, 1, 1) substr(lead_col, 1, 1)
    30 then substr(max_col, 1, 1)
    31 when substr(max_col, 1, 1) = substr(lead_col, 1, 1)
    32 and substr(max_col, 1, 2) substr(lead_col, 1, 2) then substr(max_col, 1, 2)
    33 when substr(max_col, 1, 2) = substr(lead_col, 1, 2)
    34 and substr(max_col, 1, 3) substr(lead_col, 1, 3) then substr(max_col, 1, 3)
    35 when substr(max_col, 1, 3) = substr(lead_col, 1, 3)
    36 and substr(max_col, 1, 4) substr(lead_col, 1, 4) then substr(max_col, 1, 4)
    37 when substr(max_col, 1, 4) = substr(lead_col, 1, 4)
    38 and substr(max_col, 1, 5) substr(lead_col, 1, 5) then substr(max_col, 1, 5)
    39 when substr(max_col, 1, 5) = substr(lead_col, 1, 5)
    40 and substr(max_col, 1, 6) substr(lead_col, 1, 6) then substr(max_col, 1, 6)
    41 when substr(max_col, 1, 6) = substr(lead_col, 1, 6)
    42 and substr(max_col, 1, 7) substr(lead_col, 1, 7) then substr(max_col, 1, 7)
    43 when substr(max_col, 1, 7) = substr(lead_col, 1, 7)
    44 and substr(max_col, 1, 8) substr(lead_col, 1, 8) then substr(max_col, 1, 8)
    45 when substr(max_col, 1, 8) = substr(lead_col, 1, 8)
    46 and substr(max_col, 1, 9) substr(lead_col, 1, 9) then substr(max_col, 1, 9)
    47 when substr(max_col, 1, 9) = substr(lead_col, 1, 9)
    48 and substr(max_col, 1, 10) substr(lead_col, 1, 10) then substr(max_col, 1, 10)
    49 when substr(max_col, 1, 10) = substr(lead_col, 1, 10)
    50 and substr(max_col, 1, 11) substr(lead_col, 1, 11) then substr(max_col, 1, 11)
    51 when substr(max_col, 1, 11) = substr(lead_col, 1, 11)
    52 and substr(max_col, 1, 12) substr(lead_col, 1, 12) then substr(max_col, 1, 12)
    53 else null end spine, min_col, max_col
    54 from (select ntile_range,
    55 min_col,
    56 max_col,
    57 lead( min_col) over (order by ntile_range) lead_col,
    58 lag( max_col) over (order by ntile_range) lag_col
    59 from (select ntile_range,
    60 min(column_name) min_col,
    61 max(column_name) max_col
    62 from (select column_name,
    63 ntile(15) over(order by column_name) ntile_range
    64 from ac1
    65 )
    66 group by ntile_range order by ntile_range
    67 )
    68* ) t

    SPINE MIN_COL MAX_COL
    ————————- —————————— ——————————
    A thru BITMAP A BITMAP
    BITMAPP thru C_ BITMAPPED C_OBJ#
    C1 thru De C1 Default
    D1 thru ERR_ D1 ERR_NUM
    ERRO thru GENL ERRORS GENLINKS
    GENO thru I_ GENOPTION I_AGREE
    IN thru LOB_ INTRO LOB_COL_NAME
    LOBI thru MV_QUA LOBINDEX MV_QUANTITY_SUM
    MV_QUE thru Op MV_QUERY_GEN_MISMATCH Option
    OS thru P_ OSHST P_REF_TIME
    P1 thru R_ P1 R_CONSTRAINT_NAME
    R1 thru SHORT_WAITS R1 SHORT_WAITS
    SHORT_WAIT_ thru Si SHORT_WAIT_TIME_MAX Size
    SU thru T_ SUMGROSSTURNOVER T_PER_EXEC
    T1 thru ZERO_RESULTS T1OBJID ZERO_RESULTS

    15 Zeilen ausgewählt.

    Regards

    Martin Preiss

  6. Encyclopedia Spine Problem : Ardent Performance Computing Says:

    [...] Dominic just posted a hint to the encyclopedia spine problem he posted last week. Sheesh… personally I think the hint totally gave it away. Or at least it gave away one solution. [...]

  7. Encyclopedia spine in a DWH/BI context « Klein Oracle denkraam Says:

    [...] spine in a DWH/BI context 26 05 2007 One of the blogs I read by Dominic Delmolini had an interesting question that seemed an nice exercise in the use of sql and probably analytic sql. It turns out that an [...]

  8. ora exacta Says:

    Cel mai bun articol despre Oracle Musings » Encyclopedia Spine pe care l-am citit ora exacta pana acum!

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder