Re: Sort (ORDER BY) by a comma-separated list of related fields using MySQL 4.1?

[Roland Bouman]

Hi there,

[this has got nothing to do with transactions - look in the general or newbie forum next time for questions like this]

I don't know where your table_c comes into play - I'll ignore it. Post back if it is important.

I put some extra data in order to have different values for the a_table - else it's no use: it's no fun sorting only one entry:

mysql> insert into table_b(b_name)values('kungfu master');
Query OK, 1 row affected (0.04 sec)

mysql> insert into table_b(b_name)values('analyst');
Query OK, 1 row affected (0.05 sec)

mysql> insert into table_a(a_name)values('Zoe');
Query OK, 1 row affected (0.05 sec)

mysql> insert into a_b_assoc values (2,3);
Query OK, 1 row affected (0.04 sec)

mysql> insert into a_b_assoc values (2,4);
Query OK, 1 row affected (0.04 sec)

So, now we have:

Phil: programmer, developer
Zoe: kungfu master, analyst

This query:

select a.a_name
from table_a a
inner join a_b_assoc ab
on a.id = ab.a_id
inner join table_b b
on ab.b_id = b.id
group by a_name
order by group_concat(b.b_name);

gives me:

+--------+
| a_name |
+--------+
| Zoe |
| Phil |
+--------+

you can convince yourself it really works by adding the

group_concat(b.b_name)

expression to the SELECT list.

(Actually, I was a bit surprised that mysql actually can have it as compact as this - I thought it would need something like this:

select a_name
from(

select a.a_name
, group_concat(b.b_name) b_names
from table_a a
inner join a_b_assoc ab
on a.id = ab.a_id
inner join table_b b
on ab.b_id = b.id
group by a_name

) x
order by b_names;


to make it work. It does indeed work, but so does the more compact, first syntax)


One little tip though:

> I'm sorry if that made no sense but that's as clear as I'm able to make it.

It might help if you include a sample of the desired result. Just type in exactly what you want to have your query return. Often, that's a lot easier to understand than a textual, human readable description.




Edited 1 time(s). Last edit at 02/06/2006 06:09PM by Roland Bouman.