I play fantasy football. To start each season, there is a draft, where players are randomly assigned draft order. We’ve started some seasons with a “snake” draft, to make the selection of players most fair and balanced. A snake draft works like this (a simple example with four players):
- First pick in the first round picks last in the second round.
- Second pick in the first round picks third in the second round.
- Third pick in the first round picks second in the second round.
- Last pick in the first round picks first in the second round.
- Then, in the third round, it reverts to the original order: The first pick from the first round picks first again (giving them two picks back-to-back).
Read on to see how to implement this in T-SQL. I’m happy to report that I anticipated the answer, though Aaron notes a couple of issues that I didn’t catch in my “compile the code in my head” approach.