Actually, group by does very little in this case. Sort would work just fine for this situation (see monarch's answer ... that's the type of logic that I would use.)

In these situations, I think the logic outside of the SQL is more important for the final presentation than something you can do through SQL.

So, if 'group by' isn't useful here, what is it really good for? Well, it's good for when you're trying to aggregate records, such as the following.

SELECT groupfield, SUM(somedata)
FROM tablename
GROUP BY groupfield
[download]

or

SELECT groupfield, MAX(somedata)
FROM tablename
GROUP BY groupfield
[download]

In these situations, you only get one row returned per 'group'. There's also the rarely used (in my experience) HAVING clause.

GROUP BY comes in very handy here, actually. (with MySQL's handy GROUP_CONCAT() function, versions 4.1 and up):

SELECT Group, GROUP_CONCAT(SomeData ORDER BY SomeData SEPARATOR ',')
FROM table
GROUP BY `Group`
[download]

In the OP's example, this would result in the following data that would be trivial to loop through and split on the separator chosen in your SQL:

green       3,9
orange      8
purple      4
red         1,5,6,7
yellow      2
[download]

-- Brian