# balancing male and female in random

• Dec 2, 2008, 15:46
dotJoon
balancing male and female in random
Code:

```data in myTable7 (id) sex name (1)  0  Mary (2)  1  Tom (3)  1  John (4)  0  Judy (5)  0  Jane (6)  1  Jack (7)  1  Andy (8)  1  Tim (9)  0  Liz (10) 0  Andrea (11) 1  Mikey (12) 0  Judy (13) 0  Anna data in myTable8 myGroup id (1)    8 (1)    5 (2)    1 (2)    7 (2)    11 (3)    9 (3)    13 (4)    3 (4)    2 (4)    10 (4)    12```
I have data in myTables like the above.

The code below produces the result below.
Code:

```code select name, myGroup from myTable8 left join myTable7 on myTable7.id=myTable8.id order by myGroup result (1) Tim (1) Jane (2) Mikey (2) Mary (2) Andy (3) Anna (3) Liz (4) Judy (4) Tom (4) John (4) Andrea```
I like to produce only one randomly from each myGroup.
The following code is for it and the following result is one of the results.
Code:

```code (select name, myGroup from myTable8 left join myTable7 on myTable7.id=myTable8.id where myGroup=1 order by rand() limit 1) union (select name, myGroup from myTable8 left join myTable7 on myTable7.id=myTable8.id where myGroup=2 order by rand() limit 1) union (select name, myGroup from myTable8 left join myTable7 on myTable7.id=myTable8.id where myGroup=3 order by rand() limit 1) union (select name, myGroup from myTable8 left join myTable7 on myTable7.id=myTable8.id where myGroup=4 order by rand() limit 1) union order by myGroup one of the results (1) Tim (2) Mary (3) Anna (4) John```
The one of the results has a problem, i.e it doesn't balance the male and female.

It often produces 3 females and one male like the below.

(1) Jane
(2) Mary
(3) Anna

(4) Tom

It often produces 3 males and one female like the below.

(1) Tim
(2) Andy

(3) Liz
(4) Tom

Futhermore, it sometimes produces all females like the below.

(1) Jane
(2) Mary
(3) Anna
(4) Andrea

I like to make it that the result always has balancing the number of male and the number of female, i.e. two females and one males randomly.
• Dec 2, 2008, 16:10
Aleksejs
Would it be very bad if result were just one row? :) I assume you have just 4 groups because of four UNIONs.
Code sql:

```SELECT t1.name, t2.name, t3.name, t4.name FROM myTable7 AS t1, myTable7 AS t2, myTable7 AS t3, myTable7 AS t4, myTable8 AS g1, myTable8 AS g2, myTable8 AS g3, myTable8 AS g4 WHERE g1.myGroup = 1 AND g2.myGroup = 2 AND g3.myGroup = 3 AND g4.myGroup = 4 AND g1.id = t1.id AND g2.id = t2.id AND g3.id = t3.id AND g4.id = t4.id AND (t1.sex + t2.sex + t3.sex + t4.sex = 2) ORDER BY RAND() LIMIT 1```
• Dec 2, 2008, 17:22
dotJoon
Quote:

Originally Posted by Aleksejs
Would it be very bad if result were just one row? :)

I need 4 rows because it has 4 myGroups.

The code below has no SQL errors.
Code:

```code (SELECT t1.name, t2.name, t3.name, t4.name FROM myTable7 AS t1, myTable7 AS t2, myTable7 AS t3, myTable7 AS t4,myTable8 AS g1, myTable8 AS g2, myTable8 AS g3, myTable8 AS g4 WHERE g1.myGroup = 1 AND g2.myGroup = 2 AND g3.myGroup = 3 AND g4.myGroup = 4 AND g1.id = t1.id AND g2.id = t2.id AND g3.id = t3.id AND g4.id = t4.id AND (t1.sex + t2.sex + t3.sex + t4.sex = 2) ORDER BY RAND() LIMIT 1) union (SELECT t1.name, t2.name, t3.name, t4.name FROM myTable7 AS t1, myTable7 AS t2, myTable7 AS t3, myTable7 AS t4,myTable8 AS g1, myTable8 AS g2, myTable8 AS g3, myTable8 AS g4 WHERE g1.myGroup = 1 AND g2.myGroup = 2 AND g3.myGroup = 3 AND g4.myGroup = 4 AND g1.id = t1.id AND g2.id = t2.id AND g3.id = t3.id AND g4.id = t4.id AND (t1.sex + t2.sex + t3.sex + t4.sex = 2) ORDER BY RAND() LIMIT 1) result1 Tim Jane result2 Jane Tim result3 Tim Tim result4 Jane Jane```
The result1 and the result2 is Okay, but the result3 and the result4 is not okay because it has two males or two females, futuermore it produces two same names.
• Dec 2, 2008, 17:33
Aleksejs
Well, if you use just first column from the query I wrote it will always return result from group one. My question was would you settle for four columns instead of four rows. :)
• Dec 2, 2008, 20:06
dotJoon
Quote:

Originally Posted by Aleksejs
Well, if you use just first column from the query I wrote it will always return result from group one. My question was would you settle for four columns instead of four rows. :)

I need four names which is randomly come from 4 myGroups each balancing male and female.
• Dec 3, 2008, 10:33
dotJoon
is it impossible?
(I am using mySQL 5)
• Dec 3, 2008, 11:03
r937
the question is not whether it is possible, but whether it should be done with SQL at all

there is an old joke, joon, perhaps you may not have heard it...

a man goes to the doctor and says "doc, it hurts when i do this"

and the doctor replied "so, don't do that then"

:)
• Dec 3, 2008, 11:21
dotJoon
Quote:

Originally Posted by r937
the question is not whether it is possible, but whether it should be done with SQL at all

Why might it NOT should be done with SQL?

Because is it too complex to execute ( in performance)?

Quote:

Originally Posted by r937
there is an old joke, joon, perhaps you may not have heard it...

a man goes to the doctor and says "doc, it hurts when i do this"

and the doctor replied "so, don't do that then"

:)

I think I understand the joke and what you want to say to me.
• Dec 3, 2008, 11:54
dotJoon
Quote:

Originally Posted by r937
the question is not whether it is possible, but whether it should be done with SQL at all
:)

Why might it NOT should be done with SQL?

Because is the random mating too sexually out of order?

( Actually it is from Yes or No questions instead of sexual matching.

I need to balance Yes and No question, two yes-questions and two no- questions. )
• Dec 3, 2008, 12:08
r937
run separate queries :)
• Dec 3, 2008, 12:40
dotJoon
Quote:

Originally Posted by r937
run separate queries :)

do you mean another query While a query turns?
• Dec 3, 2008, 14:33
r937
Quote:

Originally Posted by joon
do you mean another query While a query turns?

no, not nested queries :)

i meant two queries, one after the other

the first would get 2 random males, and the next would get 1 random female

simple, yes?

:)
• Dec 3, 2008, 18:57
dotJoon
Quote:

Originally Posted by r937
no, not nested queries :)

i meant two queries, one after the other

the first would get 2 random males, and the next would get 1 random female

simple, yes?

:)

I've got what I want based on your idea. Thank you.