MySQL : Return random value from each column

Question

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 ?

Do you want just one name or all the names in a random order? If you want more than one row, do you want to allow duplicates (all rows independently selected) or do you really want a shuffle (first one is random, remaining are random as long as they're not the same as any prior row)? — NYCdotNet
– NYCdotNet, Commented Dec 31, 2012 at 3:26

ysth · Accepted Answer · 2025-03-10 21:43:23Z

5

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 first_name limit $first_name_offset,1) as random_first_name, 
    (select last_name from random_names order by last_name $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. order by should be the primary key if the first/last name aren't indexed.

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;

edited Mar 10 at 21:43

answered Dec 31, 2012 at 3:30

ysth

99.1k6 gold badges126 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raptor Over a year ago

but how about list out result count equal to the number of values in original table? (just like shuffle the data in the table)

Raptor Over a year ago

can't believe a simple task needs to do so many steps. Thanks mate!

ntalbs · Accepted Answer · 2012-12-31 03:36:36Z

1

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();

answered Dec 31, 2012 at 3:36

ntalbs

29.6k8 gold badges72 silver badges86 bronze badges

2 Comments

ysth Over a year ago

unsurprisingly, that goes really slowly on a table with even just a thousand entries.

ntalbs Over a year ago

@ysth Sure it is. Using cartesian product of a thousand entries would generate a million rows. And ordering them by rand()... Yes, it it slow. But if the data set is small enough, it's simple way to get the result.

Suresh Kamrushi · Accepted Answer · 2012-12-31 03:47:41Z

1

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()

answered Dec 31, 2012 at 3:47

Suresh Kamrushi

16.2k13 gold badges79 silver badges93 bronze badges

1 Comment

ysth Over a year ago

gets only first_name and last_name from the same row, not what the question asks for

Collectives™ on Stack Overflow

MySQL : Return random value from each column

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related