How to select all rows with 2 unique columns with Sequelize.JS?

Question

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

student_id	subject_id	grade	date
Jenny	math	90	2021-12-08
Susan	math	60	2021-12-08
Jenny	math	80	2021-12-07
Susan	math	50	2021-12-07
Jenny	science	80	2021-12-08
Susan	science	90	2021-12-08
Jenny	science	76	2021-12-06
Susan	science	85	2021-12-06

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:

student_id	subject_id	grade	date
Jenny	math	90	2021-12-08
Susan	math	60	2021-12-08
Jenny	science	80	2021-12-08
Susan	science	90	2021-12-08

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

Answer 1

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