choose a random record

Question

Post reply

choose a random record

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

@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...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

xRafo SSChasing Mays Points: 623 More actions · Answer 5

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

____________________________________________________________________________
Rafo*

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

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.

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 7

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