Group By: What if I don't want to perform an aggregate function on a column?

Question

I have a table, Students, with the following columns:

________________________________________________
| id   |  name  |  class  |  date_registrered  | 
------------------------------------------------

I want to select one row for every unique class, and only the row with the largest value in date_registrered, i.e. I want to select the latest registrered Student for every class, including all the data for that one.

I tried:

SELECT id, name, class, MAX(date_registrered)
FROM Students
GROUP BY class;

I get the following error:

Column 'Students.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This question on SO adresses a simplified version of this issue. However, the example is for 2 columns only.

I only want to group by class, and I only want to perform an aggregate function on date_registrered. I also want to display all the other columns for the row with the max date_registrered for every class.

Do you know how to fix it?

If two students register on the same day, *latest registered student for every class* has no well-defined meaning. At least not in the context of returning one row per unique class. — Mike Sherrill 'Cat Recall', Nov 16 '16 at 16:21
In that case, "date_registered" deserves a more thoughtful name. — Mike Sherrill 'Cat Recall', Nov 16 '16 at 18:54

score 2 · Accepted Answer · answered Nov 16 '16 at 16:06

2

use ROW_NUMBER()

SELECT *
FROM ( SELECT id, name, class, date_registrered
              ROW_NUMBER() OVER (partition by class ORDER BY date_registrered DESC) rn
       FROM Students
     ) T
WHERE T.rn = 1

answered Nov 16 '16 at 16:06

Juan Carlos Oropeza

47,252
12
78
118

score 2 · Answer 2 · answered Nov 16 '16 at 16:07

The error message explains your issue very well, you can't perform an aggregation on one column, and not use the rest in the GROUP BY. In this case, you'll want to use something like ROW_NUMBER:

WITH CTE AS
(
    SELECT  id,
            name,
            class,
            date_registered,
            RN = ROW_NUMBER() OVER(PARTITION BY class ORDER BY date_registrered DESC)
    FROM students
)
SELECT  id,
        name,
        class,
        date_registered
FROM CTE
WHERE RN = 1;

Group By: What if I don't want to perform an aggregate function on a column?

2 Answers2