INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- Turn Off Ad Banners
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...I have tons of books, have book marked tons of tutorials, which have helped, but this forum has answered those "impossible to find" solutions. I am thrilled with this site..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft SQL Server: Programming FAQ
|
Random Numbers
|
How to SELECT *, RandomNumber AS Rnd
Posted: 9 Mar 09
|
Just a quickie Random Number solution. If anyone tried this before:
SELECT *, RAND() FROM myTable;
you will be surprised that the random number is all the same/identical.
There are ways around it, depending on what you want to do.
1. If you just want the random number for the purpose of sorting, then this will do nicely:
SELECT TOP 5 * FROM myTable ORDER BY NEWID();
2. If you need an INT random number for each row, then try this:
SELECT *, CHECKSUM(NEWID()) AS rnd FROM myTable;
*Note: The random number will range from -2,147,483,648 to 2,147,483,647. If you do not want the negative number, ABS() it.
3. If you need a FLOAT random number >0 and <=1 (the normal random number). This MAY be useful, although I think there is a more performance friendly way.
SELECT *, ABS(CONVERT( INT, CONVERT( BINARY(4), NEWID() ) ) / 2147483647.0) AS rnd FROM myTable;
-OR-
SELECT *, ABS( CHECKSUM(NEWID()) / 2147483647.0) AS rnd FROM myTable;
*Note: I have not determine for sure which is more efficient, but guts feeling tells me the data-type CONVERSION should be faster than the CHECKSUM function.
Hope these quickies help someone. have a nice day.
|
Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum
My FAQ Archive
 Email This FAQ To A Friend |
|
 |
|
