SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Aleksejs View Post
    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.

  4. #4
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Aleksejs View Post
    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.

  6. #6
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    is it impossible?
    (I am using mySQL 5)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 View Post
    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.

  9. #9
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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. )

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    run separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    run separate queries
    do you mean another query While a query turns?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by joon View Post
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.
    Last edited by dotJoon; Dec 3, 2008 at 21:20.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •