I have a table named grade with 4 columns: student_id, subject_id, grade, and date.

I would like to select all rows with only the last grade of each student for each subject. Basically, I want to select all rows with unique student_id, subject_id, like this:

Here is what I have tried:

await Grade.findAll({
    attributes: ['student_id', 'subject_id', 'grade', 'date'],
    raw: true,
    group: ['student_id', 'subject_id']
})

However, I get the following error:

SequelizeDatabaseError: column "grade.grade" must appear in the GROUP BY clause or be used in an aggregate function

CodePudding user response：

You are close. You are grouping by student_id and subject_id and you just need MAX('date').

await Grade.findAll({
    attributes: ['student_id', 'subject_id', 'grade', [Sequelize.fn('max', Sequelize.col('date')), 'date']],
    raw: true,
    group: ['student_id', 'subject_id']
})

An array in attributes can perform function and alias to a given name [function, alias].

For example:

[Sequelize.fn('max', Sequelize.col('date')), 'new_name']]

this syntax in attributes create SQL as

MAX(`date`) as `new_name`

========================================================

Update:

The above query doesn't work in Postgres.

ref: https://dba.stackexchange.com/a/194352

To achieve the same query in Postgres, 1 alternative solution is to use DISTINCT ON.

await Grade.findAll({
    attributes: [Sequelize.literal('DISTINCT ON ("student_id", "subject_id") *'),
    'id', 'student_id', 'subject_id', 'date', 'grade'],
    order: ['student_id', 'subject_id', ['date', 'DESC']]
})

More about DISTINCT ON query, please check https://zaiste.net/posts/postgresql-distinct-on/

This article also touches upon the use of window function as @sia mentioned.

Some interesting benchmark of DISTINCT ON and ROW_NUMBER https://stackoverflow.com/a/34715134/2956135