Round Robin Row Selection From SQL Server

Published on Friday, May 17, 2013

I've been trying to answer at least one question a day on Stack Overflow recently, and one came up yesterday that I thought was a pretty good little SQL problem: how can you efficiently select one row from a database in a "round robin" fashion? That is, how can you make sure the selections are evenly distributed? Turns out this can be accomplished with a single SQL query on SQL 2005 and newer using the OUTPUT clause. Assuming the table has an "Id" primary key and a "LastSelected" DateTime column, the following SQL query will select the record that hasn't been selected in the longest time (or pick an arbitrary one if there is a tie), update the last time that record was selected, and then return all columns for the record.

UPDATE MyTable
SET LastSelected = GetDate()
OUTPUT INSERTED.*
WHERE Id = (SELECT TOP (1) Id FROM MyTable ORDER BY LastSelected)