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:
I would like to have something like the following:
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.