Shuffling a list in SQL
time to read 2 min | 201 words

Let's assume that you've a table with a position column that you use for indexing or orderring, and you want to shuffle the ordering of items there. You can do it with a single query like this one:

UPDATE

Attributes
  SET Position = CASE position
                         WHEN @From THEN @To
                         ELSE Position+1 END
WHERE ReferalID = @Referal and Position BETWEEN @To AND @From

In this case, it'll switch put the row in the @from index in the @to index, and move all other rows accordingly.

The scary thing is that I learned this in the Daily WTF.

Tweet Share Share 0 comments
Tags: