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;
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