MySQL RAND Function

MySQL has a RAND function that can be invoked to produce random numbers between 0 and 1:

mysql> SELECT RAND( ), RAND( ), RAND( );

+——————+—————–+——————+

| 0.45464584925645 | 0.1824410643265 | 0.54826780459682 |

+——————+—————–+——————+

1 row in set (0.00 sec)

When invoked with an integer argument, RAND( ) uses that value to seed the random number generator. Each time you seed the generator with a given value, RAND( ) will produce a repeatable series of numbers:

mysql> SELECT RAND(1), RAND( ), RAND( );

+——————+——————+——————+

| 0.18109050223705 | 0.75023211143001 | 0.20788908117254 |

+——————+——————+——————+

1 row in set (0.00 sec)

Understanding Order by Rand()

To understand ORDER BY RAND() function, consider an employee_tbl table, which is having the following records:

mysql> SELECT * FROM employee_tbl;
+——+——+————+——————–+
| id | name | work_date | daily_typing_pages |
+——+——+————+——————–+
| 1 | John | 2007-01-24 | 250 |
| 2 | Ram | 2007-05-27 | 220 |
| 3 | Jack | 2007-05-06 | 170 |
| 3 | Jack | 2007-04-06 | 100 |
| 4 | Jill | 2007-04-06 | 220 |
| 5 | Zara | 2007-06-06 | 300 |
| 5 | Zara | 2007-02-06 | 350 |
+——+——+————+——————–+
7 rows in set (0.00 sec)

Now, use the following commands:

mysql> SELECT * FROM employee_tbl ORDER BY RAND();
+——+——+————+——————–+
| id | name | work_date | daily_typing_pages |
+——+——+————+——————–+
| 5 | Zara | 2007-06-06 | 300 |
| 3 | Jack | 2007-04-06 | 100 |
| 3 | Jack | 2007-05-06 | 170 |
| 2 | Ram | 2007-05-27 | 220 |
| 4 | Jill | 2007-04-06 | 220 |
| 5 | Zara | 2007-02-06 | 350 |
| 1 | John | 2007-01-24 | 250 |
+——+——+————+——————–+
7 rows in set (0.01 sec)

mysql> SELECT * FROM employee_tbl ORDER BY RAND();
+——+——+————+——————–+
| id | name | work_date | daily_typing_pages |
+——+——+————+——————–+
| 5 | Zara | 2007-02-06 | 350 |
| 2 | Ram | 2007-05-27 | 220 |
| 3 | Jack | 2007-04-06 | 100 |
| 1 | John | 2007-01-24 | 250 |
| 4 | Jill | 2007-04-06 | 220 |
| 3 | Jack | 2007-05-06 | 170 |
| 5 | Zara | 2007-06-06 | 300 |
+——+——+————+——————–+
7 rows in set (0.00 sec)


Few More Example:

 

mysql> SELECT RAND();
+-------------------+
| RAND()            |
+-------------------+
| 0.369500624360052 | 
+-------------------+
1 row in set (0.00 sec)

 

Related Posts

  • 69
    MySQL TRIM() function returns a string after removing all prefixes or suffixes from the given string. Syntax TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str) Arguments Name Description BOTH Indicates that prefixes from both left and right are to be removed. LEADING Indicates that only leading prefixes are to…
    Tags: string, mysql, function, select, set
  • 67
    MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start. If this position is not…
    Tags: mysql, string, select, function
  • 64
    Syntax: IF(expr,if_true_expr,if_false_expr) MySQL IF function is control flow functions that returns a value based on a condition. The IF function is sometimes referred to as IF ELSE or IF THEN ELSE function. If the expr evaluates to TRUE i.e., expr is not NULL and expr is not 0, the IF…
    Tags: function, select, mysql
  • 53
    Description: MySQL LENGTH() returns the length of a given string. Syntax: LENGTH (str) Argument: Name      Description str            A string whose length is to be returned. Example of MySQL LENGTH() function mysql> SELECT LENGTH(NULL); Result: NULL mysql> SELECT LENGTH(''); Result: 0 mysql> SELECT LENGTH('…
    Tags: mysql, select, function, string
  • 53
    MySQL SUBSTRING() returns a specified number of characters from a particular position of a given string. Syntax: SUBSTRING(str, pos, len) or SUBSTRING(str FROM pos FOR len) Arguments Name       Description str              A string. pos             Starting position.…
    Tags: mysql, select, set, function, string

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: