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

"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, May 11 '16 at 22:19
yeah i just realised that was silly of me. There is a table in Customer called Country — M.Jones, May 11 '16 at 22:22
But how would i randomly generate just one ? and also get all those other values — M.Jones, May 11 '16 at 22:22
Would me answering how to generate a random number in SQL suffice? — Webeng, May 11 '16 at 22:23
To get the random country "each month" and then generate their first last name an customer ID — M.Jones, May 11 '16 at 22:25
Using SQLite, when I use RAND() function , how do i get a customer from each month without having a month table ? — M.Jones, May 11 '16 at 23:00

Barmar · Accepted Answer · 2016-05-12T00:08:59.417

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

edited May 12 '16 at 00:08

answered May 11 '16 at 23:01

Barmar

741,623
53
500
612

will this pick a customer each month ? – M.Jones May 11 '16 at 23:03
I don't understand why you started of with cus.* – M.Jones May 11 '16 at 23:06
Because you just want the customer information, not the information from the `Country` table as well. – Barmar May 11 '16 at 23:13
@M.Jones You can use a cron job to run it every month. – Barmar May 11 '16 at 23:14
how would i get one customer each month though ? , yeah sorry i just got bit confused with the names you used – M.Jones May 11 '16 at 23:15
Do you mean there's a date column in one of the tables, and you need to group the results by month? It would help if you posted your schema, sample data, and desired results. – Barmar May 11 '16 at 23:18
its hard because its such a big database. There is no date column or anything to do with dates in the database. – M.Jones May 11 '16 at 23:20
I tried doing this SELECT FirstName, LastName, CustomerId FROM Customer JOIN Country AS c ON Country = CountryId WHERE CountryName = 'Brazil' ORDER BY RANDOM() LIMIT 1 But it came up with no results – M.Jones May 11 '16 at 23:20
Do you get anything if you leave out `ORDER BY` and `LIMIT`? – Barmar May 11 '16 at 23:23
Nope it still remains blank – M.Jones May 11 '16 at 23:24
Then you don't have any customers in Brazil. – Barmar May 11 '16 at 23:25
I do I've tried other country names aswell doesn't seem to work – M.Jones May 11 '16 at 23:26
Make a sqlfiddle with sample data. – Barmar May 11 '16 at 23:26
Not sure how to create the sqlfiddle, I'm just trying to figure out whats wrong at the moment – M.Jones May 11 '16 at 23:41
Go to www.sqlfiddle.com, paste in the statements to create and populate the tables. – Barmar May 11 '16 at 23:44
That's what I'm trying to do, too. But I can't do it without sample data. – Barmar May 11 '16 at 23:45
The tables were already created when i imported the database, how do i copy those ? – M.Jones May 11 '16 at 23:45
http://stackoverflow.com/questions/3330435/is-there-a-sqlite-equivalent-to-mysqls-describe-table – Barmar May 11 '16 at 23:47
I put the customer headings in my code above i edited it in – M.Jones May 11 '16 at 23:56
The `Country` column in `Customer` should be `INTEGER`, not `VARCHAR`. It should be the ID from the `Country` table. – Barmar May 11 '16 at 23:57
Since you put the country name in the `Customer` table instead of the ID, there's no need to join with the `Country` table at all. – Barmar May 11 '16 at 23:59
okay i see , and if you were to make it 3 random countries would you simply go WHERE Country = 'Brazil', AND "Germany" AND "Canada" Limit 3 – M.Jones May 12 '16 at 00:03
No, that won't work. When you use `AND`, a row has to satisfy all the conditions, and no single row can have 3 different countries. You need to do a separate query for each country. You can combine them with `UNION`. – Barmar May 12 '16 at 00:04
You also can't change it to `OR`. That will find customers from all 3 countries, but the random 3 that are selected won't necessarily be from different countries -- you could get all 3 from Brazil. – Barmar May 12 '16 at 00:05
okay thanks ! I'll get onto that and then try do it so its chosen every month – M.Jones May 12 '16 at 00:07
I managed to do it using IN operator and it works fine. Just need to do the dates, which seems bit harder – M.Jones May 12 '16 at 00:13
@M.Jones That's the same as `OR`. It won't get one customer from each country. It will get 3 customers from all 3 countries. – Barmar May 12 '16 at 00:14
I changed the Limit to 1 so it only gets one random – M.Jones May 12 '16 at 00:18
Yeah I'm trying to find a way to have it pick one customer from every month without creating the month table. – M.Jones May 12 '16 at 00:19
You're not making sense. How can it pick a customer from a month if there's no month in the table. – Barmar May 12 '16 at 00:20
What month are you talking about? The month that the customer was created, the month that they made a purchase? If the latter, you need to join with the purchase table. – Barmar May 12 '16 at 00:21
so I need to create a column in customer called month ? – M.Jones May 12 '16 at 00:22
Or a column called `CreatedDate`, and then use `MONTH(CreatedDate)` to get the month. – Barmar May 12 '16 at 00:24

generating random value in Sql

1 Answers1