Mysql, select row on a max condition and show associated columns

Question

I have

select max(id), timestamp from mytable;

This returned the max-id over the data, but the timestamp doesn't seem to be the one matching the row. I got

+----------+--------------------------+
| max(id) | from_unixtime(timestamp) |
+----------+--------------------------+
|  1429517 | 2015-01-01 00:00:15      |
+----------+--------------------------+

This is the correct max-id, but the timestamp belongs to a different record

Answer 1

Try like this:

SELECT id, timestamp FROM mytable
WHERE id = (SELECT MAX(id) FROM mytable)

ie get the max(id) using a subquery and then get the corresponding data from the table.

Answer 2

You can do this with order by and limit:

SELECT id, timestamp
FROM mytable
ORDER BY id DESC
LIMIT 1;

This should be quite efficient with an index on id or id/timestamp.