mysqlsqlhibernate

MySQL - Get the latest record for a given list of column values

I have the following table structure:

Request table:

 id  insert_time           account_id
 ------------------------------------
 1  2018-04-05 08:06:23       abc
 2  2018-09-03 08:14:45       abc
 3  2018-08-13 09:23:34       xyz
 4  2018-08-04 09:25:37       def
 5  2018-08-24 11:45:37       def

I need to find the latest records for account IDs abc and def. I don't care about xyz.

I tried to get the results using group by and inner join methods but was not successful in limiting the results to just the user list I care about. Please advice

Update: Thanks everyone for your feedback. Appreciate it! I needed the entire row as the output. I have used id column instead of timestamp to get the latest record since it's auto-incremented. This is what I finally came up with that gave me the output I need:

select t.* FROM table t
join (select max(table.id) as maxNum from table 
where account_id in ('abc','def') group by account_id) tm on t.id = tm.maxNum;
Solution

  • I think this is what you were looking for

      select account_id,max(insret_time)
  from table where account_id in ('abc', 'def')
  group by account_id