Can I use non-aggregate columns with group by?

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
);

​x
 
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;

 
SELECT kind, MAX(age)FROM stuffGROUP 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;

 
SELECT id, kind, MAX(age)FROM stuffGROUP 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

 
SELECT stuff.*FROM   stuff,   ( SELECT kind, MAX(age)     FROM stuff     GROUP BY kind) maxesWHERE   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

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

Advertisement

Answer