Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Return random records in a table-valued function? RE: Return random records in a table-valued function?

  • January 3, 2014 at 2:37 am

    #1677155

    pietlinden (1/2/2014)

    Jeff,

    I think that's how I got the random number of records per enrollment so that the distribution is irregular. I noticed that Chris's code returns the same number of results per Protocol. I guess this is where I have to re-read parts of Guru's Guide to T-SQL - might be an old book, but it's easy to follow and he deals with a lot of the standard problems in T-SQL (like this one).

    Think I'll read that and post back. Otherwise I may have to resort to the dreaded cursor... (might have to try it just to see how bad it is!) Might work okay as I only need it to run once ...

    Pieter

    The sample code works, and I know the theory behind it works too - I regularly use something similar for returning a random number of rows at random from a list of words to construct a random sentence:

    -- Returns about 100,000 rows per second

    WITH

    [4] AS (SELECT n = 0 FROM (VALUES (0), (0), (0), (0)) d (n)),

    [16] AS (SELECT n = 0 FROM [4] a, [4] b),

    [256] AS (SELECT n = 0 FROM [16] a, [16] b),

    [65536] AS (SELECT n = 0 FROM [256] a, [256] b),

    iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM [65536] a, [16] b)

    SELECT TOP 10000

    -- Shamelessly nicked from JBM's random data generator

    t.n,

    -- 10 years worth of dates ranging from 1/1/2000 to 12/31/2009

    [RandomDate] = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

    -- 100 different account numbers

    [RandomAccount] = ABS(CHECKSUM(NEWID()))%100+1,

    -- Amounts from -99.99 to + 99.99

    [RandomValue] = CAST(CHECKSUM(NEWID())%10000 /100.0 AS MONEY),

    -- Randomised number of words picked randomly from list

    iTVF.Sentence

    FROM iTally t

    CROSS APPLY ( -- note 'n' in the TOP expression forces evaluation for every row

    SELECT TOP ((t.n+ABS(CHECKSUM(NEWID())))%10+1)

    [data()] = word

    FROM (VALUES ('the'),('quick'), ('brown'), ('fox'), ('jumped'), ('over'), ('the'), ('lazy'), ('dog')) Words (word)

    ORDER BY NEWID()

    FOR XML PATH('')

    ) iTVF(Sentence)

    I'm not sure what the problem is but if you post up the code you've used I'm sure we can work something out.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden