Variation on greatest-n-per-group where whole table is needed with indicator, not just greatest rows per group 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 &quot;greatest n per group&quot;, 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 &quot;greatest&quot; indicator. But since those attempts are focused on the only the row with the greatest value, when the part of the where clause like &quot;table2.date IS NULL&quot; 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 &lt; 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.
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.