Generating Random Numbers?
-
I have a need to generate a random number in a SQL select statement. This number is to be used for ordering the selection of rows returned in the selection. The desired effect would be to have the resulting recordset sorted randomly and differently each time the selection is generated.
The wrinkle to this is that only a certain subset of records will use this sorting method. The case is thus:
The recordswill first be sorted by a field "City" so that all will be first sorted by this value, there is aslo a boolean value and in the case that this is "True" I want to sort by the random number. Where the boolean is false, I want to sort by the customer's name. I can handle thist portion of the issue without issue, the problem is with the random number being generated in the SQL select statement.
I've tried generating random numbers with the RAND() function using a number of different seed values, but when executing it in a select statement, the random values all return the same value, due to the fact that the execution of the SQL is so fast, that the seed value remains constant.
Can anyone provide a solution?
-
Try NEWID() for ORDER BY, it isn't a number but does have the desired effect.
-
it's nothing to do with the speed of SQL...or the seed number.
RAND() is evaluated once per result-set
NEWID() is evaulated once per row.
-
SELECT *
FROM YourTable
ORDER BY City, CASE YourBoolean WHEN 1 THEN NEWID() ELSE CustName END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply