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)

[Please don't post screenshots of text](https://meta.stackoverflow.com/a/285557/354577). They can't be searched or copied and offer poor usability. Instead, paste the code as text directly into your question. If you select it and click the `{}` button or Ctrl+K the code block will be indented by four spaces, which will cause it to be rendered as code. — ChrisGPT was on strike, Sep 21 '20 at 00:13
I am not putting in work in something that is closed. I would be happy to adapt the form of the Q if it would be accessible to others to learn from. — user3504575, Sep 23 '20 at 11:07
This question is closed, but _not_ deleted. Questions closed as duplicates (like this one) are often useful to future users since they provide additional search targets. If you have the above content in a file the only "work" you'd have to do is copy that text, [edit] this question, paste the text over the image, select the code again, and press Ctrl+K or click the `{}` button to format it as code. — ChrisGPT was on strike, Sep 23 '20 at 15:54

score -1 · Accepted Answer · edited Sep 19 '20 at 12:57

-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

edited Sep 19 '20 at 12:57

user3504575

525
1
6
19

answered Sep 19 '20 at 12:47

GMB

216,147
25
84
135

would the multi-column index be preferable to using the Id column? Instead of using `where time = ...` use `where id = ...` and select the Id of the latest task for the user? – user3504575 Sep 19 '20 at 12:57
@user3504575: both options are OK, depending on how you define the *latest* task. – GMB Sep 19 '20 at 13:12

Mysql group by aggregation sort and limit

1 Answers1