What happened to my day, Dominic?

Normally, I rely on a strengthening cup of Starbucks’ venti vanilla skinny latte and a bowl of Dorset Cereals (you can take the boy out of Dorset, but not the boy out of Dorset) to get my brain faintly ticking in the morning (all freebies gratefully received).

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

No problem, I thought, I’ll just rattle this off. If only. What happened to my day?

It was interesting to note my slow but steady degradation from just rattling it off to oh-bollocks-just-get-something-that-bloody-works.

In the end, I think I overdosed a bit on the WITHs, but I think what I’ve done does the job even if it’s not the smartest way. I’m sure that I’ve missed a number of tricks along the way but hopefully on the scale of smartypants to dumbass, I’ve somewhere in the middle not too close to the dumbass end. It’s everyone’s fear that they end up being ridiculed on Oracle WTF. And quite often, one gets so used to doing complex things that one starts to overlook simple and common sense approaches.

Eventually, the approach I went for was to break the extremes of each bucket into all the possible substrings and match them against the previous extreme of the previous bucket and find the first substring that was different.

I’m not sure that I’ve explained that very well, if that’s even possible. But given bucket 1 from A – ABACUS and bucket 2 from ABRACADABRA – ANTELOPE, I proposed to break ABACUS into A, AB, ABA, ABAC, ABACU and ABACUS and compare that with ABRACADABRA broken into A, AB, ABR, ABRA, etc which would let me determine that bucket 1 was from A -> ABA and bucket 2 was from ABR -> etc.

Hopefully, you get what I mean.

Anyway, I didn’t find it that easy. Found a few hidden features in my implementation along the way. And I’m sure I missed some tricks along the way. Here it is:

For the record I mostly copied the idea from the paper Dominic linked – I was thinking along the same lines as you until he posted that link. I wouldn’t have thought of it on my own. Since I didn’t actually come up with a query on my own, I find this more impressive. ;)