I need to export data from a database with a new random field not present in the database. Something like:
SELECT field1, field2, rand(1,5) as field3 FROM table
It is possible?
Add a comment
|
4 Answers
Yes, RAND() returns a value between 0 and 1. Use math to make that number be within the range you want.
Comments
Not a MySQL answer, but may be of use: on SQL Server the 'RAND' function requires a seed that must differ between rows. Therefore a simple way may be:
SELECT field1, field2, rand(id) as field3 FROM table
This is dependant upon an ID being available, and it is also only pseudo-random; the same value for field3 will be output each time. To work around this, simply increment id by a time-based value, for example using the technique in this post:
SELECT field1, field2, rand(id + datediff(s, '19700101', getdate())) as field3 FROM table
This will give a value between 0 and 1, and a bit of simple mathematics can then force that to be between 1 and 5 if that's what's required.
Comments
You can use this in MYSQL:-
SELECT field1,field2,CAST((RAND() * 3) + 1 AS UNSIGNED) as Randomnumber from tablename
It will generate a random number within a range.
Comments
I solved this way:
CEIL( RAND( ) *5 ) AS field3
Thank you all!