M.Jones M.Jones - 6 months ago 12
SQL Question

generating random value in Sql

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

"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"

Answer

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