MySQL timestamp and GROUP BY with two tables

Question

I have a table with 3 fields:

• ID (not unique, not primary key)
• timestamp
• marker

I want to get the last entry for every distinct ID, but when I do
SELECT ID, max(timestamp) from table GROUP BY ID
the marker field is wrong.

I have another table with the following fields:

• ID (unique, primary key)
• lat
• lng

I would like to perform the same query but with the lat and lng fields as well, but since I can't do the first step, I don't know what kind of query should I use. I've been trying with no success.

Could someone point me in the right direction? Thanks a lot.

Answer 1

Having an ID column that is not unique sounds "unexpected", but this one should get you the expected rows:

SELECT t.id, t.timestamp, MAX( t.marker ) marker,
       t2.lat, t2.lng
FROM table t
JOIN (
    SELECT id, MAX(timestamp) ts
    FROM table
    GROUP BY id
) tx ON ( tx.id = t.id AND tx.ts = t.timestamp )
JOIN t2 ON ( t2.id = tx.id )
GROUP BY t.id, t.timestamp

The second group by will make sure that you only get one row in case there are more records for the same id and timestamp.

Update: Edited query to join t2.

Use LEFT JOIN in case there are ids in t1 that do not exist in t2. lat and lng would then be NULL for that rows.