Tuesday, December 2, 2008
Getting the most recent row for each id
I was working on configTracker today (a pet project to watch server configurations for changes and do alerting, reporting, etc...) and ran into a scenario where I needed to find the most recent row in a table for a given id. The table has a column called aud_ts of type timestamp. and a column call host_id of type integer. After several iterations (my SQL skills are waining after not developing for 5 or so years....) I came up with: select host_id, max(aud_ts) from myTable group by host_id; Then, I can join this table against all the others in the system on host_id and aud_ts and know I'm getting the most recent info for this server.