Aggregate relation value group by relation out in SurrealDB

I'm trying to find a way to get aggregated data for relations grouped by relation out.

Here's my setup: I have 2 students (A and B) and 3 subjects (X, Y and Z). Student take tests on a subject and get a score. They can take multiple tests for the same subject, we keep a history of the scores but we are only interested in their highest for a given subject.

Here's my database initialization:

> use NS stackoverflow

stackoverflow> use DB example

stackoverflow/example> CREATE student:A;
stackoverflow/example> CREATE subject:X;
stackoverflow/example> CREATE subject:Y;
stackoverflow/example> CREATE subject:Z;
stackoverflow/example> RELATE student:A->scores->subject:X SET score = 4;
stackoverflow/example> RELATE student:A->scores->subject:X SET score = 6;
stackoverflow/example> RELATE student:A->scores->subject:Y SET score = 9;
stackoverflow/example> RELATE student:A->scores->subject:Y SET score = 8;
stackoverflow/example> RELATE student:A->scores->subject:Z SET score = 5;
stackoverflow/example> CREATE student:B;
stackoverflow/example> RELATE student:B->scores->subject:X SET score = 6;
stackoverflow/example> RELATE student:B->scores->subject:X SET score = 8;
stackoverflow/example> RELATE student:B->scores->subject:Y SET score = 2;
stackoverflow/example> RELATE student:B->scores->subject:Z SET score = 42;

Now I can do:

stackoverflow/example> select id, math::max(->scores.score) from student;
[[{ id: student:A, "math::max": 9 }, { id: student:B, "math::max": 42 }]]

Now here's my question: how can I (if at all possible) get the maximum score per-subject for each student? Something that would return:

[[
  {
    id: student:A,
    "->scores": [
      {
        out: subject:X,
        score: 6
      },
      {
        out: subject:Y,
        score:9
      },
      {
        out: subject:Z,
        score: 5
      }
    ]
  },
  {
    id: student:B,
    "->scores": [
      {
        out: subject:X,
        score: 8
      },
      {
        out: subject:Y,
        score: 2
      },
      {
        out: subject:Z,
        score: 42
      }
    ]
  }
]]

Solution

What I didn't realize is that by having relations ->scores between students and subjects, I can actually query scores as if it was a table:

select * from scores

[[
  {
    id: scores:0mkbkn6095vi5u0yg598,
    in: student:A,
    out: subject:Y, score: 8
  },
  {
    id: scores:14kytkjyybpzrqper4bh,
    in: student:A,
    out: subject:Y,
    score: 9
  },
  ...
]]

Once I figured that out, I found that the simple query:

select math::max(score), in, out from scores group by in, out;

give me the result I was after:

[[{ in: student:A, "math::max": 6, out: subject:X }, { in: student:A, "math::max": 9, out: subject:Y }, { in: student:A, "math::max": 5, out: subject:Z }, { in: student:B, "math::max": 8, out: subject:X }, { in: student:B, "math::max": 2, out: subject:Y }, { in: student:B, "math::max": 42, out: subject:Z }]]