2

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.

Peter Lang
  • 54,264
  • 27
  • 148
  • 161
echedey lorenzo
  • 377
  • 1
  • 5
  • 19
  • Dup: http://stackoverflow.com/questions/612231/sql-select-rows-with-maxcolumn-value-distinct-by-another-column, http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns, http://stackoverflow.com/questions/121387/sql-fetch-the-row-which-has-the-max-value-for-a-column, http://stackoverflow.com/questions/1299556/sql-group-by-max, http://stackoverflow.com/questions/2109941/querying-for-a-unique-value-based-on-the-aggregate-of-another-value-while-groupin, http://stackoverflow.com/questions/723054/extra-fields-with-sql-min-group-by – outis Mar 08 '10 at 09:33

1 Answers1

2

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.

Peter Lang
  • 54,264
  • 27
  • 148
  • 161
  • Great! Thanks a lot for your answer. The reason ID is not unique in the table is because I need all the entries for all the transactions a client ID does. I'll test your query once at home. How could join it to a second table table2 that has the same IDs (but unique) to get its lat and lng fields that are only in this second table? Thanks – echedey lorenzo Mar 08 '10 at 09:47
  • @charo: Edited my answer to add columns from `t2`. – Peter Lang Mar 08 '10 at 10:10
  • Thanks Peter, I'm willing to test it at home :) – echedey lorenzo Mar 08 '10 at 10:36
  • In that case you should `accept` my answer. You could up-vote it too, but that's your decision of course :) – Peter Lang Mar 08 '10 at 12:15