Anyway, you may not have heard, but I'm afraid I have some very sad news. Sneezy passed away last night… he had the mother of all sneezes just before bed, and I'm afraid he ka-chooed his brains out. Memorial services are next Tuesday.
Sneezy's will stipulated that all of his possessions should be randomly split as evenly as possible among the remaining 6 dwarfs. (He figured Snow White had all she needed being married to that rich prince dude). The split would occur based on quantity rather than value (since the gems they find are priceless, after all).
And so, one of the dwarfs (I'll let you guess who), wrote up a T-SQL script to evenly divide Sneezy's possessions among his surviving buddies. It was actually a pretty simple (yet clever) bit of code:
with Sneezys_Stuff asHey, is the NTILE function way cool or what? The CTE uses NTILE to evenly assign a value of 1 to 6 to all of Sneezy’s stuff and that NTILE value is used to determine the new owner (1=Dopey, 2=Doc, and so on). And ORDERing BY NEWID() spreads Sneezy’s estate in a random manner.
,InheritingNTileValue=ntile(6) over (order by newid())
set OwningDwarf=case InheritingNTileValue
when 1 then 'Dopey'
when 2 then 'Doc'
when 3 then 'Grumpy'
when 4 then 'Bashful'
when 5 then 'Sleepy'
when 6 then 'Happy'
Now can you guess which dwarf wrote this script? I’ll give you a hint. Let’s say that Sneezy had 27152 items attributed to him. NTILE (6) will divide that number by 6 to come up with 27152 / 6 = 4525 items, with a remainder of 2 (or 27152 % 6). Guess which NTILE value(s) get the remainder? That’s right… the remainder is distributed one-by-one to the NTILE value of 1, then 2, and so on. So in this case, Dopey and Doc will get that little something extra... they’ll get 4526 items while the other poor saps will only get 4525.
Yep, you guessed it… Dopey wrote this. I guess that whole “Dopey” guise is a sham… “Greedy” is more like it. Chances are 5 in 6 (83%) that Dopey will get something extra. Happy, on the other hand, with an NTILE value of 6 will NEVER get something extra. But I guess one could argue that he doesn’t need it anyway.
However, Happy is not going to stand idly by. He slaps Dopey upside the head (and of course he’s happily smiling as he does it) and says he has a fairer way to distribute Sneezy’s stuff. He sits down and whips out this script:
with Dwarf_Vals asHe randomly assigns a value of 1 to 6 to each dwarf (via the ROW_NUMBER() function) and that is joined to the values of 1 to 6 that NTILE assigns to Sneezy’s property. Another window function to the rescue.
,DwarfValue=row_number() over (order by newid())
from (select 'Dopey' union all
select 'Doc' union all
select 'Grumpy' union all
select 'Bashful' union all
select 'Sleepy' union all
select 'Happy') Dwarfs (Dwarf)
,InheritingNTileValue=ntile (6) over (order by newid())
join Dwarf_Vals on Sneezys_Stuff.InheritingNTileValue=Dwarf_Vals.DwarfValue;
Grumpy makes one final suggestion. He says that they shouldn’t hard-code the 6 in the NTILE function. He suggests the following instead:
ntile ((select max(DwarfValue) from Dwarf_Vals)) over (order by newid())Grumpy explains that this is more flexible because you never know when another dwarf might suddenly bite the dust. He says this as he looks at Dopey with a malicious grin.