Can I use non-aggregate columns with group by?

Question

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

I would however like access the one of the non-aggregates associated with the max. In plain english, I want a table with the oldest id of each kind.

CREATE TABLE stuff (
   id int,
   kind int,
   age int
);

This query gives me the information I'm after:

SELECT kind, MAX(age)
FROM stuff
GROUP BY kind;

But it's not in the most useful form. I really want the id associated with each row so I can use it in later queries.

I'm looking for something like this:

SELECT id, kind, MAX(age)
FROM stuff
GROUP BY kind;

That outputs this:

SELECT stuff.*
FROM
   stuff,
   ( SELECT kind, MAX(age)
     FROM stuff
     GROUP BY kind) maxes
WHERE
   stuff.kind = maxes.kind AND
   stuff.age = maxes.age

It really seems like there should be a way to get this information without needing to join. I just need the SQL engine to remember the other columns when it's calculating the max.

Answer 1

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