CODEWITHSUNDEEP
mysqlsqlrandom
Helba
Helba

Reputation: 63

Random Order by Group in Random Order

I have 5 tables in mysql forming 4 one to many relationships. The tables are last_name, parent, child, grandchild, and city. Last name has many parents, parent has many children, child has many grandchildren, and city has many grandchildren.

I'm needing to get the grandchildren(in a certain city) in a random order but grouped by last_name(that is, on different rows but consecutively) but with last_name in random order. So basically.

Last_name     GrandChild
    B            3
    B            56
    B            9
    D            2
    D            67
    C            38
    C            20
    A            14

I have this so far

 SELECT ln.id, gc.id
        FROM parent p
        INNER JOIN child ch
        ON ch.parent_id = p.id
        INNER JOIN grandchild gc
        ON ch.id = gc.child_id
        WHERE gc.city_id = 3

That just gives me the layout and relationships I need, but I'm not sure where to go from there. Is this possible? And if so, what commands/techniques do I need to look into?

Upvotes: 3

Views: 178

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I assume by "grouped" you mean that you want each grandchild on a separate line, but all with the same last name on consecutive rows.

    SELECT ln.id, gc.id
    FROM parent p INNER JOIN
         child ch
         ON ch.parent_id = p.id INNER JOIN
         grandchild gc
         ON ch.id = gc.child_id inner join
         (select ln.*, rand() as rand
          from lastname ln
         ) ln
         on p.lastname = ln.lastname
    WHERE gc.city_id = 3
    order by ln.rand

In other words, this assigns a random number to the last name, and then uses it for the order by. By the way, your request is ambiguous. If you want the ordering by parent rather than last name, you would do the subquery on parent.

Upvotes: 3

juergen d
juergen d

Reputation: 204766

    SELECT gc.lastname, group_concat(p.lastname) as grandchildren
    FROM parent p
    INNER JOIN child ch ON ch.parent_id = p.id
    INNER JOIN grandchild gc ON ch.id = gc.child_id
    WHERE gc.city_id = 3
    group by gc.lastname

Upvotes: 0

Related Questions