To select a single winner at random from the combined results of the three tables : RAND « Function « SQL / MySQL






  1. SQL / MySQL
  2. Function
  3. RAND

To select a single winner at random from the combined results of the three tables

     
mysql>
mysql>
mysql> CREATE TABLE prospect
    -> (
    ->  fname   CHAR(40),
    ->  lname   CHAR(40),
    ->  addr    CHAR(40)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE customer
    -> (
    ->  last_name       CHAR(40),
    ->  first_name      CHAR(40),
    ->  address CHAR(40)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE vendor
    -> (
    ->  company CHAR(60),
    ->  street  CHAR(30)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO prospect (fname, lname, addr)
    ->  VALUES
    ->          ('Peter','Jones','482 Main St., Apt. 402'),
    ->          ('Bernice','Smith','916 Maple Dr.')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO customer (first_name, last_name, address)
    ->  VALUES
    ->          ('Grace','Peterson','16055 First Ave.'),
    ->          ('Bernice','Smith','916 Maple Dr.'),
    ->          ('Walter','Brown','8602 1st St.')
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO vendor (company, street)
    ->  VALUES
    ->          ('Database, Inc.','38 Third Ave.'),
    ->          ('Xml, Ltd.','213B Commerce Park.')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
    -> UNION
    -> SELECT CONCAT(last_name,', ',first_name), address FROM customer
    -> UNION
    -> SELECT company, street FROM vendor
    -> ORDER BY RAND( ) LIMIT 1;
+-----------------+------------------+
| name            | addr             |
+-----------------+------------------+
| Peterson, Grace | 16055 First Ave. |
+-----------------+------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> drop table prospect;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table customer;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table vendor;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Each time you seed the generator with a given value, RAND( ) will produce a repeatable series of numbers:
2.Selection with a random Column
3.Generating Random Numbers
4.Seed RAND( ) randomly
5.Randomizing a Set of Rows
6.Add a column of random numbers to the column output list, alias it, and refer to the alias for sorting
7.Selecting Random Items from a Set of Rows
8.To draw five winning entries at random from a table named drawing that contains contest entries, use RAND( ) i
9.get some random numbers
10.Get some random numbers in the range 1-100
11.Get the name and the number of each player whose name consists of the pattern m.n. The point can be any random