Q:

MySQL aggregation by strings

I have an activities table like below (also contains some other columns):
+------------------+---------------+------------+
| data             | event         | logged_on  |
+------------------+---------------+------------+
| 12345            | File          | 2015-04-08 |
| 25232            | Bulletin      | 2015-04-08 |
| 7890             | File          | 2015-04-08 |
| youtube          | Search        | 2015-04-07 |
| 13568            | Bulletin      | 2015-04-07 |
| 25232            | Bulletin      | 2015-04-07 |
| 7890             | File          | 2015-04-07 |
+------------------+---------------+------------+

I want to fetch unique data values for latest date. So the required result would be:
+------------------+---------------+------------+
| data             | event         | logged_on  |
+------------------+---------------+------------+
| 12345            | File          | 2015-04-08 |
| 25232            | Bulletin      | 2015-04-08 |
| 7890             | File          | 2015-04-08 |
| youtube          | Search        | 2015-04-07 |
| 13568            | Bulletin      | 2015-04-07 |
+------------------+---------------+------------+

But a select distinct on all the three columns above returns the same value of data for different dates. I cannot remove logged_on from the select query as it's required elsewhere.
In MySQL, i tried using the following query:
SELECT DISTINCT
  data,
  event,
  MAX(logged_on) AS latest_log_date
FROM
  activites
WHERE
  username = %%(username)s
GROUP BY
  data
ORDER BY id DESC, latest_log_date DESC
LIMIT %%(limit)s
;

But this does not seem to work. I'm getting the some result, which looks like it's correct, but it isn't. And i'm not able to reason about it. Any ideas?

A:

Use NOT EXISTS to return only rows that has no later with same data/event.
SELECT data, event, logged_on AS latest_log_date
from activites a1
where not exists (select 1 from activites a2
                  where a1.data = a2.data
                    and a1.event = a2.event
                    and a2.logged_on > a1.logged_on)

I'm not quite sure if this is what you want. Perhaps you need to remove and a1.event = a2.event from the sub-query.

