1

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.

3
  • 1
    What version of mysql? This is not more complicate than a normal greatest-n-per-group query. Mar 17 '21 at 21:48
  • We are using mariadb 10.2.34 and 10.3.13. Mar 18 '21 at 16:18
  • As Bill Karwin shows in his answer, it is quite simple with window functions. You could also use the window aggregate: SELECT MAX(date) OVER (PARTITION BY repid) = date AS greatest, ... Mar 18 '21 at 21:27
2

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.

1
  • We are using mariadb 10.2.34 and 10.3.13. And in fact have used the ROW_NUMBER function in some other connections. Mar 18 '21 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.