hellogoodnight hellogoodnight - 17 days ago 6
SQL Question

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

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?

Answer

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