Round Robin Row Selection From SQL Server

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)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s