Sep

27

Randomize SQL Results

Written by Duncan White

Many developers struggle with creating a simple randomized dataset. There are a few ways that this can be done, but most of them involve creating functions and manipulating the Random function to make this work; this can become very complex.

I am going to show you a quick and easy way to create a randomized dataset all in T-SQL. You will use the NEWID() function, instead of the RAND() function, to generate a random ID. You will NOT use the RAND() function because putting the RAND() function as a column in your SELECT statement will return the exact same value for each row, making randomization of your query results not possible.

Select
ToolTipID, ToolTipHeader, ToolTipDescription, NewID() As Random
From
Tooltips
Order By
Random

The above TSQL will allow for a random GUI to be created and ordered by.

Tags: ,

One Response to “Randomize SQL Results”

  1. Jon Fazzaro Says:
    October 7th, 2008 at 9:14 am

    Thanks, Duncan. This is a totally snappy trick. As it turns out, we can also apply it to LINQ to SQL queries:

    ToolTip randomTooltip = (from t as ToolTip in MyDataContext.ToolTips
    orderby System.Guid.NewGuid()
    select t).FirstOrDefault();

    …and while we’re at it, any collection of things we can throw at LINQ:

    char randomChar = (from c in “abcdefghijklmnopqrstuvwxyz”
    orderby System.Guid.NewGuid()
    select c);

Leave a Reply