Re^2: mysql sorting

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]

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.

Comment on Re^2: mysql sorting Select or Download Code

Replies are listed 'Best First'.
Re^3: mysql sorting by bpphillips (Friar) on Jun 01, 2005 at 13:07 UTC
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	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^3: mysql sorting
by bpphillips (Friar) on Jun 01, 2005 at 13:07 UTC

GROUP_CONCAT() function

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

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

[reply]
[d/l]
[select]