r/mysql•
Sys_man

[help]selecting max(update_time) of a series of results.

I'm querying a log table (that logs excel macro uses) which contains data like the following: macro_name | user_name | branch | update_time

I'm trying to return a list of all the distinct macro_names that a branch has run, and only select the newest update_time of each macro_name

Getting a list of all macros a branch has run is simple:

select  * from macro_log_table
where branch = <branch>

But I get results like this:

macro_1 | user1 | <branch> | 2020-01-06 09:19:08
macro_1 | user2 | <branch> | 2020-02-16 09:19:08
macro_1 | user3 | <branch> | 2020-01-26 09:19:08
macro_2 | user1 | <branch> | 2020-03-01 09:19:08
macro_2 | user1 | <branch> | 2020-02-26 09:19:08
macro_2 | user4 | <branch> | 2020-01-15 09:19:08
macro_3 | user5 | <branch> | 2020-03-12 09:19:08
macro_3 | user4 | <branch> | 2020-02-12 09:19:08
macro_1 | user6 | <branch> | 2020-01-10 09:19:08

What I'd like is:

macro_1 | user2 | <branch> | 2020-02-16 09:19:08
macro_2 | user1 | <branch> | 2020-03-01 09:19:08
macro_3 | user5 | <branch> | 2020-03-12 09:19:08

I'm probably missing something simple, but grouping by update_time doesn't guarantee the newest time

Posted Mar 20, 2020, 2:32 AM UTC · edited

16
[deleted]•

[deleted]

1
seko45•

select a.* FROM macro_log_table AS a LEFT JOIN macro_log_table AS b ON b.macro_name=a.macro_name AND b.branch=a.branch AND b.update_time > a.update_time WHERE b.macro_name IS NULL and a.branch=<branch>

1
Sys_manOP•

oh... the guy who commented deleted his comment, but it helped and got me there. I was going to say:

brilliant, max(update_time) + group by macro_name got me there. Knew it would be something simple! Thanks!

1
seko45•

that was me, I was grouping by user_name and realized that's not what you wanted, so deleted and posted a correction (if you want the username of the row w/ the last update time, something like the left join method I posted is needed)

1
Sys_manOP•

yeah, I had to slightly change it. I don't really care who the user is, just wanted the max update time of each macro for a single branch. But your comment did get me to the answer so thanks :)

2
isotopp•

Herr is a systematic list of all the ways to answer your question.

https://jan.kneschke.de/projects/mysql/groupwise-max/

1
r3pr0b8•
SELECT t.macro_name
     , t.user
     , t.branch
     , t.update_time
  FROM ( SELECT macro_name
              , MAX(update_time) AS latest
           FROM macro_log_table
          WHERE branch = <branch>
         GROUP
             BY macro_name ) AS m
INNER
  JOIN macro_log_table AS t
    ON t.macro_name = m.macro_name
   AND t.update_time = m.latest
1