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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.