Question 306722 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL multiple joins to get a single result set (with mysql max function)

I am trying to use a max(..) to get the maximum value from a set, but I want to also get the other row values that correspond to this row (or rows if there are multiple rows that match the same "max"). I’m using MySQL 8.

I was able to part of the way using this SQL (a solution provided by SelVazi in this related post: Get value from the same row as an aggregate function (max) in MySQL using SELECT query)

However, now things have gotten more complicated. I’m splitting tasks into a separate table, so that I only need to save the task ID values in the "user_tasks" table.

In situations where there are multiple tasks with the same "max" datetime, I’d like to have all those task names listed in the result set (with task_duration values)

This is the SQL that I’m currently working with:

SELECT u.*
FROM user_tasks u
INNER JOIN (
  SELECT user_id, max(login_time) as last_login_time, task_duration 
  FROM user_tasks
  WHERE user_id IN (123, 456, 789) 
  group by user_id
) AS s ON s.user_id = u.user_id and s.last_login_time = u.login_time;

Here is the DB schema & some sample values:

create table user_tasks (
    user_id int(11), 
    login_time datetime, 
    task_id int(11), 
    task_duration int(11)
)

create table tasks (
    task_id int(11),
    task_name varchar(50)
)

insert into user_tasks values (123, '2023-01-30 06:10:03', 1, 50);
insert into user_tasks values (123, '2023-02-25 06:10:03', 2, 45);

# please note below there are duplicate date values (so both are "max")

insert into user_tasks values (123, '2023-05-30 06:10:03', 3, 60);
insert into user_tasks values (123, '2023-05-30 06:10:03', 2, 20);

insert into user_tasks values (456, '2023-03-30 06:10:03', 3, 50);
insert into user_tasks values (456, '2023-02-25 06:10:03', 1, 20);
insert into user_tasks values (456, '2023-01-30 06:10:03', 2, 10);

insert into user_tasks values (789, '2023-03-30 06:10:03', 3, 35);
insert into user_tasks values (789, '2023-01-30 06:10:03', 1, 38);
insert into user_tasks values (789, '2023-05-30 06:10:03', 2, 26);

insert into user_tasks values (898, '2023-05-30 06:10:03', 1, 16);
insert into user_tasks values (900, '2023-05-30 06:10:03', 2, 18);

# and now, the tasks table...

insert into tasks values (1, 'walk dog');
insert into tasks values (2, 'bathe dog');
insert into tasks values (3, 'feed dog');

The above SELECT statement yields this result:

123,'2023-05-30 06:10:03', 3, 60
123,'2023-05-30 06:10:03', 2, 20
456,'2023-03-30 06:10:03', 3, 50
789,'2023-05-30 06:10:03', 2, 26

However, I would like it to yield this result:

123,'2023-05-30 06:10:03','feed dog', 60
123,'2023-05-30 06:10:03','bathe dog', 20
456,'2023-03-30 06:10:03','feed dog', 50
789,'2023-05-30 06:10:03','bathe dog', 26

Also, if anyone can think of a better title for this post, please suggest one and I’ll gladly edit it to better reflect the question. Thanks!

Tags:

2

Answers


  1. 0

    The best way to do this in modern MySQL 8.0 or later is to use a window function:

    SELECT user_id, login_time, task_name, task_duration
FROM (
  SELECT user_id, login_time, task_name, task_duration,
    RANK() OVER (PARTITION BY user_id ORDER BY login_time DESC) AS _rank
  FROM user_tasks
  JOIN tasks USING (task_id)
  WHERE user_id IN (123, 456, 789)
) AS t
WHERE _rank = 1;

    Result given your test data:

    +---------+---------------------+-----------+---------------+
| user_id | login_time          | task_name | task_duration |
+---------+---------------------+-----------+---------------+
|     123 | 2023-05-30 06:10:03 | feed dog  |            60 |
|     123 | 2023-05-30 06:10:03 | bathe dog |            20 |
|     456 | 2023-03-30 06:10:03 | feed dog  |            50 |
|     789 | 2023-05-30 06:10:03 | bathe dog |            26 |
+---------+---------------------+-----------+---------------+

    If you’re not using a version of MySQL that supports window functions, it’s time to upgrade. All MySQL 5.x versions have passed their end-of-life date.

    Login or Signup to reply.
  2. 0

    Just join on the additional table…

    SELECT
    u.user_id,
    u.login_time,
    tasks.task_name,
    u.task_duration 
FROM
  user_tasks u
INNER JOIN
(
  SELECT
    user_id, max(login_time) as last_login_time, task_duration 
  FROM
    user_tasks
  WHERE
    user_id IN (123, 456, 789) 
  GROUP BY 
    user_id
) 
  AS s 
    ON  s.user_id = u.user_id
    AND s.last_login_time = u.login_time
----------------------------------------
-- Added this
----------------------------------------
INNER JOIN 
  tasks 
    ON tasks.task_id = u.task_id;

    Or more concisely…

    WITH
  ranked_user_task AS
(
  SELECT
    *,
    DENSE_RANK() OVER (
      PARTITION BY user_id
          ORDER BY login_time DESC
    ) 
      AS user_task_rank
  FROM
    user_tasks
)
SELECT
    u.user_id,
    u.login_time,
    t.task_name,
    u.task_duration 
FROM
  ranked_user_task   AS u
INNER JOIN 
  tasks              AS t
    ON t.task_id = u.task_id
WHERE
      u.user_id IN (123, 456, 789)  
  AND u.user_task_rank = 1
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search