Post reply

choose a random record

  • December 29, 2011 at 10:34 am

    #1426577

    Dev (12/29/2011)

    It will repeat but not toooo frequently.

    3 times in 10 rows in one test I did.

    Does random mean not to repeat the value in 50 attempts? It is impractical requirement and we are trying to satisfy it.

    It's not impractical. It's not the definition of random, but it's not impractical or even particularly difficult.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 29, 2011 at 10:38 am

    #1426580

    Oh, another way to get 50 random rows, one at a time with no repeats:

    CREATE TABLE #Random (

    Seq INT IDENTITY,

    name_emp varchar(50)

    )

    INSERT INTO #Random (name_emp)

    SELECT TOP (50) name_emp FROM Employees

    ORDER BY NewID()

    Then you can just query the rows one at a time, as such:

    SELECT name_emp FROM #Random WHERE Seq = 1;

    SELECT name_emp FROM #Random WHERE Seq = 2;

    SELECT name_emp FROM #Random WHERE Seq = 3;

    ....

    SELECT name_emp FROM #Random WHERE Seq = 50;

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 30, 2011 at 7:54 am

    #1426962

    @xRafo: Congratulations!!! You got what you wanted. 🙂

    Now would you please explain me the requirement in details? I would like to know the business case where random values (for example employee names) are required.

    I am trying to guess but my fatty brain is not giving me any clue...

  • December 30, 2011 at 8:03 am

    #1426969

    Dev (12/30/2011)

    Now would you please explain me the requirement in details? I would like to know the business case where random values (for example employee names) are required.

    The electronic version of drawing names from a hat (and not replacing the names). Random prizes usually, 'employees of the day' on a portal, etc, etc.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 30, 2011 at 8:12 am

    #1426976

    Yes, well it's a draw end of the year awards to employees..

    ____________________________________________________________________________
    Rafo*

  • December 30, 2011 at 8:14 am

    #1426980

    GilaMonster (12/30/2011)

    Dev (12/30/2011)

    Now would you please explain me the requirement in details? I would like to know the business case where random values (for example employee names) are required.

    The electronic version of drawing names from a hat (and not replacing the names). Random prizes usually, 'employees of the day' on a portal, etc, etc.

    Valid Scenarios but I believe we can suggest better solutions (at design level) because these are recurring events.

    I am still interested in OP's case.

  • January 3, 2012 at 11:43 am

    #1427921

    If it's a prize drawing you're going to want a weighted system with your employee id set with the highest weight. 😛

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply