Heigh-Ho! Heigh-Ho! With NTILE We Will Go!

It's not commonly known that the Seven Dwarfs use SQL Server to keep track of the precious gems that they find in the mine. They catalog and record each stone, and, believe it or not, they have a "Finders Keepers" policy. Whoever uncovers a gem with his pickaxe gets to keep it. Not surprisingly, Sleepy has the least amount of stones to his name, and Happy has the most. (Hey, how do you think Happy got his name? The guy’s swimming in it!)

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 as
(
  select OwningDwarf
        ,InheritingNTileValue=ntile(6) over (order by newid())
  from DwarfPossessions
  where OwningDwarf='Sneezy'
)
update Sneezys_Stuff
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'
                 end;

Hey, 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.

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 as 
(
  select Dwarf
        ,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)
)
,Sneezys_Stuff as
(
  select OwningDwarf
        ,InheritingNTileValue=ntile (6) over (order by newid())
  from DwarfPossessions
  where OwningDwarf='Sneezy'
)
update Sneezys_Stuff
set OwningDwarf=Dwarf_Vals.Dwarf
from Sneezys_Stuff
join Dwarf_Vals on Sneezys_Stuff.InheritingNTileValue=Dwarf_Vals.DwarfValue;

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

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.