vegeta vegeta - 2 months ago 13
Java Question

mysql select last result in IN query

I am a beginner in mysql, I have two table. Log table contains (email,datetime,value) and another is tmp in which email exist.

I make query

select distinct email, datetime
from Log
where email in (select email from tmp)
group by email;


in result of this is :

| email | datetime |
+----------------------------+---------------------+
| aaa@gmail.com | 2014-02-06 14:08:28 |
| bbb@gmail.com | 2014-05-22 18:53:39 |
| cc@gmail.com | 2014-05-22 18:51:19 |
+----------------------------+---------------------+


But the I need datetime should be latest, this query pick the first date (older). What should I change to get the latest date?

Answer

Due the fact you use a group by you can use an aggregation function like max( ) if you want the more recent otherwise you can use min()

  select email, max(datetime )
  from Log 
  where email in (select email from tmp) 
  group by email;

and using group by you don't need distinct