MySql Group By and order by date

Question

Here is my table data:

id      page_id     time            created
=======================================================
1       1           00:15:00        2020-11-05 09:55:54
2       1           00:25:00        2020-11-10 07:35:24
3       2           00:10:25        2020-11-06 06:15:20

and here is the MySql query:

SELECT
  a.* SUM (a.time) AS time
FROM
  `activity` AS a
GROUP BY a.page_id
ORDER BY a.created DESC;

The desired result is to have the latest record showing on top, but instead, Im getting first record being on top;

Like:

2       1           00:25:00        2020-11-10 07:35:24
3       2           00:10:25        2020-11-06 06:15:20

instead I'm getting this:

1       1           00:15:00        2020-11-05 09:55:54
3       2           00:10:25        2020-11-06 06:15:20

Answer 1

You seem to want the latest record per id, as defined by created. If so, you need to filter rather than aggregate.

In MySQL 8.0, I would suggest window functions:

select *
from (
    select a.*,
        rank() over(partition by page_id order by created desc) rn
    from activity a
) a
where rn = 1
order by created desc

In earlier versions, you can filter with a correlated subquery:

select a.*
from activity a
where a.created = (
    select max(a1.created) from activity a1 where a1.page_id = a.page_id
)