Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to SELECT *, RandomNumber AS Rnd

Random Numbers

How to SELECT *, RandomNumber AS Rnd

by  fhlee  Posted    (Edited  )
Just a quickie Random Number solution. If anyone tried this before:
[color blue]SELECT *, RAND() FROM myTable;[/color]
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:
[color blue]SELECT TOP 5 * FROM myTable ORDER BY NEWID();[/color]

2. If you need an INT random number for each row, then try this:
[color blue]SELECT *, CHECKSUM(NEWID()) AS rnd FROM myTable;[/color]
*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.
[color blue]SELECT *, ABS(CONVERT( INT, CONVERT( BINARY(4), NEWID() ) ) / 2147483647.0) AS rnd FROM myTable;[/color]
-OR-
[color blue]SELECT *, ABS( CHECKSUM(NEWID()) / 2147483647.0) AS rnd FROM myTable;[/color]
*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.



Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top