How to get max value by group?

[BananaMasterz]

I have a table that is like:

name:  nick, score: 9
name:  nick, score: 23
name:  nick, score: 2
name:  john, score: 2
name:  john, score: 92

How can I get the max score of each person? So the result would be:

nick: 23
john: 92

[simolus3]

As you may have guessed, this is what GROUP BY can do. So the query would look like this:

final maxScore = table.score.max();
final query = selectOnly(table)
    ..addColumns([table.name, maxScore])
    ..groupBy([table.name]);

for (final row in await query.get()) {
  final name = row.read(table.name)!;
  final score = row.read(maxScore)!;
}

[BananaMasterz]

ah yes, wasn't sure how to use that, thanks!