zet zet - 3 months ago 10
MySQL Question

How to get the corresponding id of a max field in mysql

I am still new to mysql commands and I learned how to get the max value of a column and count of a column. My database is named db and my table (named "foo") looks like this:

+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | varchar(64) | NO | PRI | NULL | |
| expires | datetime | YES | MUL | NULL | |
| extra | text | YES | | NULL | |
| valid | tinyint(1) | NO | | NULL | |
| trust_id | varchar(64) | YES | MUL | NULL | |
| user_id | varchar(64) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+


Basically, I want to know if there is a way to get the max date of the expires field and then print out the corresponding fields like user_id and id. So far this is the command I currently use to get the max date from expires in the command line

mysql -u root db -e "select max(id) from foo;"


this alone will give me:

+---------------------+
| max(expires) |
+---------------------+
| 2016-08-05 17:54:44 |
+---------------------+


What I would like to do is get this back:

+---------------------+--------+--------+
| max(expires) | id | user_id|
+---------------------+--------+--------+
| 2016-08-05 17:54:44 |cd97eb4 | 2bf2cec|
+---------------------+--------+--------+


So then I would have the max expiration date along with the id and user id for that expiration date.

Any insight is greatly appreciated!

Answer

If you just want the id and user_id corresponding to that (max) date, then something like this should do

select expires, id, user_id
from foo
where expires in
    (select max(expires) 
     from foo)
Comments