group by, sort problem

[snoodle]

My SQL is not so hot...

Consider the simple table, "test", which has 2 columns, "type" and "name".

TYPE - NAME

fruit - cherry
fruit - apple
fruit - banana
dog - terrier
dog - poodle

I would like to group by type, and order by type, then name (actually, I'd like to pick out the 1st item of each group (first, according to my secondary order col).

SELECT * FROM `test` GROUP BY type order by type ASC, name ASC
yields...


TYPE - NAME
dog - terrier
fruit - cherry

...when I expected...

TYPE - NAME
dog - poodle
fruit - apple
It appears to ignore the secondary order column.

I've had the same problem using distinct.

[Fumigator]

MySQL shouldn't even let you select name if it's not in the GROUP BY clause. Other RDBMSs (DB2, Oracle, etc) throw an error if you try. You're GROUPING BY the type-- the resulting rows are a summary of all rows for each type. How do you pick a name out of a summarized group? You can't.

You can, however, use min(name) or max(name) to pick the lowest or highest name out of the group (according to how you are collating), which seems like it will give you what you want (using min(), that is).

Putting name 2nd on the ORDER BY won't do anything since the first sort column, type will never be duplicated (since types are getting grouped).

[snoodle]

thank you!