CODEWITHSUNDEEP
sql
wav
wav

Reputation: 1552

Generate field in MySQL SELECT with random value

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?

Upvotes: 0

Views: 255

Answers (4)

wav
wav

Reputation: 1552

I solved this way:

CEIL( RAND( ) *5 ) AS field3

Thank you all!

Upvotes: 0

Vivek Sadh
Vivek Sadh

Reputation: 4268

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.

Upvotes: 0

Adrian Wragg
Adrian Wragg

Reputation: 7401

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.

Upvotes: 0

Matthew
Matthew

Reputation: 25763

Yes, RAND() returns a value between 0 and 1. Use math to make that number be within the range you want.

MySQL RAND Documentation

Upvotes: 3

Related Questions