Variation on greatest-n-per-group where whole table is needed with indicator, not just greatest rows per group

Question

I have a query that returns some rows having a grouping on a column and dates. I would like to have that query indicate in a column which of each group has the greatest date.

There are a vast number of questions on how to do the "greatest n per group", but those questions only want the row from each group with the greatest value. I want all the rows, and just an indication of which is the greatest.

For example, here is a table:

id	rep	date
1	27058	2020-11-16
2	27058	2020-11-09
3	27058	2020-10-30
4	46029	2020-11-03

I would like to have something like the following:

id	rep	date	greatest
1	27058	2020-11-16	1
2	27058	2020-11-09	0
3	27058	2020-10-30	0
4	46029	2020-11-03	1

I have experimented with some greatest-n-per-group methods to add a "greatest" indicator. But since those attempts are focused on the only the row with the greatest value, when the part of the where clause like "table2.date IS NULL" is removed, extra rows are added with all the join combinations.

Here is an example of such an experiment that does not work. It tries to use the greatest-n-per-group but comments out the IS NULL line:

SELECT DISTINCT m1.usermessageid, m1.repid, m1.sender_role, m1.message, m1.datesent, m2.datesent
FROM usermessage m1
LEFT JOIN usermessage m2 ON m1.principalid = m2.principalid and m1.repid = m2.repid AND m1.datesent < m2.datesent
WHERE m1.principalid = 99831
AND m1.repid IN (21600,99214,27058,97360,92678,96184,55429,46029)
# AND m2.datesent IS NULL
ORDER BY m1.datesent desc;

I would like to avoid having to add an additional query just to get the greatest rows.

Answer 1

Use a window function:

SELECT ROW_NUMBER() OVER (PARTITION BY repid ORDER BY date DESC) = 1 AS greatest, ...

This requires MySQL 8.0.02 or MariaDB 10.2.0 to use window functions.