generating random value in Sql

Question

I want to pick one customer each month from the three countries, Germany, Canada, Brazil (table name Country). I need to generate their first , last name and their ID which is in another table called "customer". I had this idea to start of with but wasn't 100% if I was on right track.

SELECT DISTINCT CountryID FROM Country WHERE CountryName IN('Germany','Canada','Brazil')

Country ID	CountryName
1	Brazil
2	Germany
3	Canada
4	Norway
5	Czech Republic
6	Austria
7	Belgium

The customer table

col_pos	col_name	col_type	mandatory		primary
0	CustomerId	INTEGER	1	NULL	1
1	FirstName	VARCHAR(40)	1	NULL	0
2	LastName	VARCHAR(20)	1	NULL	0
3	Company	VARCHAR(80)	0	NULL	0
4	Address	VARCHAR(70)	0	NULL	0
5	City	VARCHAR(40)	0	NULL	0
6	State	VARCHAR(40)	0	NULL	0
7	Country	VARCHAR(40)	0	NULL	0
8	PostalCode	VARCHAR(10)	0	NULL	0
9	Phone	VARCHAR(24)	0	NULL	0
10	Fax	VARCHAR(24)	0	NULL	0
11	Email	VARCHAR(60)	1	NULL	0
12	SupportRepId	INTEGER	0	NULL	0

Ok so you want to obtain the first name, last name, and id of someone in table customer, and you want to pick them randomly from table customer. So why do we need table Country? is there a column called Country in the Customer table? please elaborate, and it would be nice to see table customer — Webeng
– Webeng, Commented May 11, 2016 at 22:19
yeah i just realised that was silly of me. There is a table in Customer called Country — M.Jones
– M.Jones, Commented May 11, 2016 at 22:22
But how would i randomly generate just one ? and also get all those other values — M.Jones
– M.Jones, Commented May 11, 2016 at 22:22
Would me answering how to generate a random number in SQL suffice? — Webeng
– Webeng, Commented May 11, 2016 at 22:23

Barmar · Accepted Answer · 2016-05-12 00:08:59Z

1

The RAND() function generates a random number. You can use it in ORDER BY to select a random rows matching some criteria.

SELECT * FROM ( ( SELECT FirstName, LastName, CustomerId FROM Customer WHERE Country = 'Brazil' ORDER BY RANDOM() LIMIT 1 ) UNION ( SELECT FirstName, LastName, CustomerId FROM Customer WHERE Country = 'Germany' ORDER BY RANDOM() LIMIT 1 ) UNION ( SELECT FirstName, LastName, CustomerId FROM Customer WHERE Country = 'Canada' ORDER BY RANDOM() LIMIT 1 ) )