Can I use non-aggregate columns with group by?

I think it's tempting indeed to ask the system to solve the problem in one pass rather than having to do the job twice (find the max, and the find the corresponding id). You can do using CONCAT (as suggested in Naktibalda refered article), not sure that would be more effeciant

SELECT MAX( CONCAT( LPAD(age, 10, '0'), '-', id)
FROM STUFF1
GROUP BY kind;

Should work, you have to split the answer to get the age and the id. (That's really ugly though)

You can't get the Id of the row that MAX found, because there might not be only one id with the maximum age.

You cannot (should not) put non-aggregates in the SELECT line of a GROUP BY query.

You can, and have to, define what you are grouping by for the aggregate function to return the correct result.

MySQL (and SQLite) decided in their infinite wisdom that they would go against spec, and allow queries to accept GROUP BY clauses missing columns quoted in the SELECT - it effectively makes these queries not portable.

It really seems like there should be a way to get this information without needing to join.

Without access to the analytic/ranking/windowing functions that MySQL doesn't support, the self join to a derived table/inline view is the most portable means of getting the result you desire.