Encyclopedia Spine
May 15th, 2007 — ddelmoliWith 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 |
May 16th, 2007 at 4:21 pm
[...] Today, however, a poser from Dominic Delmolino also kicked things off. [...]
May 18th, 2007 at 7:34 am
[...] 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. [...]
May 18th, 2007 at 5:06 pm
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
May 20th, 2007 at 11:50 am
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
May 21st, 2007 at 4:21 pm
it’s not pretty, it’s not generic – but it seems to work … (as long as you know the maximal length of your strings)
Regards
Martin Preiss
May 22nd, 2007 at 12:41 am
[...] 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. [...]
June 27th, 2008 at 8:56 am
[...] 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 [...]