Pick One Random Number from a Set of Numbers

A while back I posted a means to generate random numbers in SQL. I ran across a situation where I need to pick one number randomly from a set of numbers.

I found a decent solution here, but I didn't want to create a separate view and scalar function for this task.

Instead I create a table variable with a single integer column as the primary key. Next, I insert each of the values of the small set of numbers into the table variable. Finally, I perform a SELECT against the table variable with NEWID() specified in the ORDER BY clause, so that a randomly-chosen member of the set will be returned. 

        DECLARE @Code int

        DECLARE @Codes TABLE
        (
            Code int PRIMARY KEY
        )
       
        INSERT INTO @Codes (Code) VALUES (3)  
        INSERT INTO @Codes (Code) VALUES (6)
        INSERT INTO @Codes (Code) VALUES (9)
        INSERT INTO @Codes (Code) VALUES (12)

        IF @Code IS NULL
            SELECT TOP 1 @Code = Code FROM @Codes ORDER BY NEWID()

  
In my case this is to provide a stored procedure, which may or may not have a value provided for the @Code input parameter, be able to automatically pick a random value in case @Code is NULL. The table variable isn't necessary if the value being picked is among the primary key values of an existing table; a random selection can be obtained simply by doing a SELECT with NEWID().

Random Numbers in SQL

I needed a SQL query to insert a range of values randomly into each record of a table variable.

The SELECT statement would use the random number in conjunction with a CASE statement to pick from two different values to insert into a field, in effect tagging records randomly with one value or the other. 

My first attempt involved trying to grab the one's digit of the second of today's date via the getdate() function, and based on whether this value is even or odd, pick one or another of the values to assign to a field for the record I insert.

INSERT INTO @MyTableVariable (MyField)
SELECT

    CASE
        WHEN RIGHT(DATEPART(s, getdate()), 1) IN (0, 2, 4, 6, 8) THEN 32
        WHEN RIGHT(DATEPART(s, getdate()), 1) IN (1, 3, 5, 7, 9) THEN 39
    END AS MyField

FROM dbo.MyTable t

The result wasn't quite what I expected. Instead of one of each inserted value appearing randomly in the resulting table variable for MyField, the same value appeared each and every time.

As it turns out, I didn't realize that in the CASE statement, the seconds portion of the current time, the "seed" if you will, only got set once at the beginning of the SELECT. There is nothing in the statement to prompt the function to update the current time with every iteration, every record examined from MyTable, it only performs a one-off examination of the seconds and goes with that for the rest of the operation.


My solution involved using instead the one's digit of the integer primary key of the source table, like so.

INSERT INTO @MyTableVariable (MyField)
SELECT
    CASE                  
        WHEN RIGHT(t.ID, 1) IN (0, 2, 4, 6, 8) THEN 32        -- Even
        WHEN RIGHT(t.ID, 1) IN (1, 3, 5, 7, 9) THEN 39        -- Odd            
    END AS MyField

FROM dbo.MyTable t

In this case, the subset of data that I'm querying from MyTable is just that, a subset, which means that the primary key (ID) won't be in any particular order; there are plenty of opportunities for the one's digit to be odd or even. This provides a reasonably random sampling of records in the resulting table variable.