CODEWITHSUNDEEP
mysqlsql
abhink
abhink

Reputation: 9136

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?

Upvotes: 0

Views: 66

Answers (3)

Hector Montero
Hector Montero

Reputation: 129

SELECT data, event, logged_on
FROM activities A1
WHERE logged_on = (SELECT A2.logged_on FROM activities A2
                  WHERE A2.data = A1.data AND A2.event = A1.event
                  ORDER BY A2.logged_on DESC LIMIT 1);

Upvotes: 2

Abhishek Ranjan
Abhishek Ranjan

Reputation: 26

Maybe i have not got your question clear, but this query is giving me your required output

select data,event,logged_on from activities group by data order by logged_on desc;

Upvotes: 0

jarlh
jarlh

Reputation: 44796

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.

Upvotes: 2

Related Questions