List:General Discussion« Previous MessageNext Message »
From:Basil Daoust Date:July 21 2009 8:04pm
Subject:Re: Hard? query to with group order by group head's name
View as plain text  
> On Thu, Jul 16, 2009 at 1:20 AM, Elim PDT<elim@stripped> wrote:
>> My table group_member looks like this:
>> +-----------+-------+---------+
>> | member_id | name | head_id |
>> +-----------+-------+---------+
>> | 1 | Elim | NULL |
>> | 2 | Ann | 1 |
>> | 3 | David | NULL |
>> | 4 | John | 3 |
>> | 5 | Jane | 3 |
>> +-----------+-------+---------+
>>
>> Record with null head_id means
>> the member is a group head.
>> Record with head_id k are in the
>> group with head whoes id equals k.
>>
>> I like to fetch the rows in the following ordaer
>>
>> | 3 | David | NULL |
>> | 4 | John | 3 |
>> | 5 | Jane | 3 |
>> | 1 | Elim | NULL |
>> | 2 | Ann | 1 |
>>
>> That is
>> (1) A head-row follewed by the group members with that head
>> (2)head rows are ordered alphabetically by name.
>>
>> What the query looks like?
>>
>> Thanks

I hope this is not a school assignment.

What I came up with was to create a new order column that I populated  
with the name of the HEAD.
Then I can order by the head, head_id, and the member_id

mysql> select t1.member_id, t1.name, t1.head_id from (
select m1.*, IF ( m2.name IS NULL, m1.name, m2.name) as groupName from  
group_member as m1
left outer join group_member as m2 ON ( m1.head_id = m2.member_id )
order by groupName, m1.head_id, m1.member_id ) AS t1;
+-----------+-------+---------+
| member_id | name  | head_id |
+-----------+-------+---------+
| 3         | David | NULL    |
| 4         | John  | 3       |
| 5         | Jane  | 3       |
| 1         | Elim  | NULL    |
| 2         | Ann   | 1       |
+-----------+-------+---------+
5 rows in set (0.01 sec)

It seemed to work without the order by member_id but I'll assume that  
is a fact of the small sample size.


Thread
Client deleted database, semi high priority master slave questionScott Haneda8 Jul
  • Re: Client deleted database,semi high priority master slave questionbmurphy8 Jul
  • RE: Client deleted database, semi high priority master slave questionDaevid Vincent8 Jul
    • Re: Client deleted database, semi high priority master slave questionScott Haneda8 Jul
    • Re: Client deleted database, semi high priority master slave questionScott Haneda8 Jul
  • Re: Client deleted database, semi high priority master slave questionBaron Schwartz9 Jul
  • Hard? query to with group order by group head's nameElim PDT16 Jul
    • Re: Hard? query to with group order by group head's nameDarryle Steplight16 Jul
    • what outputs/illustrates a table's structure? - WAS: Re: Hard? query to with group order by group head's nameGovinda16 Jul
      • Re: what outputs/illustrates a table's structure? - WAS: Re: Hard? query to with group order by group head's nameMarcus Bointon16 Jul
    • Re: Hard? query to with group order by group head's nameBrent Baisley21 Jul
  • Re: Client deleted database, semi high priority master slave questionrussbucket16 Jul
  • Re: Hard? query to with group order by group head's nameElim PDT21 Jul
    • Re: Hard? query to with group order by group head's nameBasil Daoust21 Jul
Re: what outputs/illustrates a table's structure? - WAS: Re: Hard? query to with group order by group head's nameGovinda16 Jul
  • Re: what outputs/illustrates a table's structure? - WAS: Re: Hard? query to with group order by group head's nameMarcus Bointon16 Jul