On Thu, Jul 16, 2009 at 1:20 AM, Elim PDT<e...@pdtnetworks.net> 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 > >
You need to create your own sort values, and link to the "head" name. So really you are sorting on head name + head_id. Since sometimes the "head" name is the current record, sometimes it's a "parent" record, you need to conditional check which type of "record" it is and built the sort value. SELECT tablename.*, IF(tablename.head_id=NULL, CONCAT(tablename.name, tablename.member_id), CONCAT(heads.name, tablename.head_id) ) AS SortValue FROM tablename LEFT JOIN tablename AS heads ON tablename.head_id=heads.member_id ORDER BY SortValue Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
