Reputation: 54212
Note: This might be a strange question.
I have a table containing first name and last name, which schema as follow (table name: random_names):
id INT PRIMARY, AUTO INCREMENT
first_name VARCHAR(100) NOT NULL
last_name VARCHAR(100) NOT NULL
I would like to use a query to fetch a random value from first_name
and last_name
. Currently I use 2 queries to fetch the value:
SELECT first_name FROM random_names ORDER BY rand()
SELECT last_name FROM random_names ORDER BY rand()
But I wish I can output a list of random results in 1 result output. What did I miss ?
Upvotes: 2
Views: 1637
Reputation: 98388
select
(select first_name from random_names order by rand() limit 1) as random_first_name,
(select last_name from random_names order by rand() limit 1) as random_last_name;
though for tables of any size it is much faster if you programmatically determine the number of entries and pick a random offset for each column:
select
(select first_name from random_names order by rand() limit $first_name_offset,1) as random_first_name,
(select last_name from random_names order by rand() limit $last_name_offset,1) as random_last_name;
where the offsets are a random number from 0 to one less than the result of select count(*) from random_names
.
Followup question:
but how about list out result count equal to the number of values in original table? (just like shuffle the data in the table)
I'd do that like this:
create temporary table rand_last (id int(11) primary key auto_increment, last_name text) select last_name from random_names order by rand();
create temporary table rand_first (id int(11) primary key auto_increment, first_name text) select first_name from random_names order by rand();
select first_name, last_name from rand_first inner join rand_last using (id);
or possibly like this (assuming random_names has an 'id' primary key):
create temporary table rand_one (id int(11) primary key auto_increment, random_names_id int(11)) select id random_names_id from random_names order by rand();
create temporary table rand_two (id int(11) primary key auto_increment, random_names_id int(11)) select id random_names_id from random_names order by rand();
select rand_first.first_name, rand_last.last_name from rand_one inner join rand_two using (id) inner join random_names rand_first on rand_one.random_names_id=rand_first.id inner join random_names rand_last on rand_two.random_names_id=rand_last.id;
Upvotes: 4
Reputation: 16086
You can also do like this-
SELECT first_name, last_name FROM random_names ORDER BY rand()
OR
SELECT concat(first_name," ",last_name) fullname FROM random_names ORDER BY rand()
Upvotes: 1
Reputation: 29448
You can get all possible pairs of first_name
and last_name
in random order by following query:
select *
from (select first_name from random_names) a,
(select last_name from random_names) b
order by rand();
Upvotes: 1