The Blog of Ian Mercer.

SQL Random Order - some unexpected benefits

In this StackOverflow answer I explained the technique used by my home automation system to generate a random ordering for songs during playback.

By using a column called 'Shuffle' containing random integer values it becomes easy to generate a repeatable random ordering of the records in a database table. The query to access the table is ...

Random random = new Random();
int seed = random.Next(); 
result = result.OrderBy(s => (~(s.Shuffle & seed)) & (s.Shuffle | seed));    // ^ seed);

Which uses an XOR operation in the database and orders by the results by the result of that operation.

This approach has several advantages as I outlined in my Stackoverflow answer:

Efficient: SQL handles the ordering, no need to fetch the whole table Repeatable: (good for testing) - can use the same random seed to generate the same random order Works on most (all?) Entity Framework supported databases

In my home automation system I calculate a new random seed each day and maintain the same seed for the whole day. This means I can play a playlist (actually there are no playlists, only tags, but that's another story) and reliably get back to the same point in it.

One advantage that I hadn't considered when I first added this feature is that because the shuffle column is across all songs and because playlists are just tags on songs I can now ensure that you only hear each song once a day even if it occurs in multiple playlists! In fact, I can simply calculate the value of shuffle XOR today's random seed and then request only songs that are greater than that value making it easy to continue any playlist from any point without having to remember any state other than a single value.

This is the approach used by my home automation system to randomize playlists. It picks a new seed each day giving a consistent order during the day (allowing easy pause / resume capabilities) but a fresh look at each playlist each new day.

Related Stories

SQL Server - error: 18456, severity: 14, state: 38 - Incorrect Login

A rant about developers using the same message for different errors

Ian Mercer
Ian Mercer