Mysql group by aggregation sort and limit

Question

I am trying to figure out a seemingly trivial SQL query. For all users in the table I want to find the time and data for the row with the highest time (latest event).

The following almost solves it

SELECT user, MAX(time) as time FROM tasks GROUP BY user;

The problem is of course that the data column cannot be reduced. I think therefore I should use a WHERE or ORDER BY + LIMIT construction. But I am too far out of my domain here to know how this should be done properly. Any hints?

Note. It is not possible to use GROUP BY in this instance because I want to select on the table row ID, which cannot be aggregated, obviously.

-- MYSQL

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE tasks (
    id int AUTO_INCREMENT,
    user varchar(100) NOT NULL,
    time date NOT NULL,
    data varchar(100) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO tasks (user, time, data) VALUES
    ("Kalle", "1970-01-01", "old news"),
    ("Kalle", "2020-01-01", "latest shit"),
    ("Pelle", "1970-01-01", "regular data");

-- Expected output
-- +----+-------+------------+--------------+
-- | id | user  | time       | data         |
-- +----+-------+------------+--------------+
-- |  2 | Kalle | 2020-01-01 | latest shit  |
-- |  3 | Pelle | 1970-01-01 | regular data |
-- +----+-------+------------+--------------+
-- 2 rows in set (0.00 sec)

Answer 1

You can filter with a subquery:

select t.* 
from tasks t
where time = (select max(t1.time) from tasks t1 where t1.user = t.user)

This query would take advantage of a multi-column index on (user, time).

In MySQL 8.0, you can also solve this top-1-per-group with window functions:

select *
from (select t.*, row_number() over(partition by user order by time desc) rn from tasks t) t
where rn = 1