SQL

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.

Read more...

open source (18) ASP.NET (15) ASP.NET MVC (14) static site generator (9) programming (8) Azure (7) Wyam (7) Roslyn (7) NuGet (6) devops (5) .NET Compiler Platform (5) Razor (4) personal (4) Vue.js (4) Entity Framework (4) LINQ (4) database (4) KendoUI (4) KendoUI MVC (4) grid (4) csharp (3) scripting (3) meta (3) T4 (3) XML (3) Mono (3) GtkSharp (3) tools (2) Cake (2) msbuild (2) magic strings (2) Azure Cosmos DB (2) Azure Functions (2) LINQ to Entities (2) strings (2) algorithms (2) LINQPad (2) blog (2) CSS (2) export (2) CSV (2) HtmlHelper (2) Entity Framework Code First (2) Nxdb (2) XQuery (2) Blazor (1) WebAssembly (1) Netlify (1) FTP (1) documentation (1) configuration (1) DSL (1) enum (1) stdin (1) stream (1) console (1) cli (1) npm (1) node (1) microdependencies (1) collections (1) concurrent (1) HashSet (1) Twitter (1) Serilog (1) MiniProfiler (1) logging (1) OWIN (1) templating (1) design (1) web (1) JavaScript (1) API (1) IIS (1) debugging (1) LINQ to SQL (1) FluentBootstrap (1) Bootstrap (1) RazorDatabase (1) GitHub (1) AppVeyor (1) fluent interfaces (1) method chaining (1) style (1) conventions (1) PDF (1) Acrobat (1) Excel (1) checkbox (1) postback (1) icon fonts (1) icons (1) SQL (1) SQL Server (1) round robin (1) DotNet (1) Dictionary (1) MultiDictionary (1) data annotations (1) persistence (1) object persistence (1) NiceThreads (1) Threading (1) Monitor (1) ReaderWriterLockSlim (1) locking (1) ILocker (1) networkdays (1) weekdays (1) ButtonPressEvent (1) Context Menu (1) ContextMenuHelper (1) Menu (1) Popup (1) Popup Menu (1) CellRenderer (1) TreeModel (1) TreeView (1) TreeViewColumn (1)