How to use group by to get only one row

Question

I have this query:

  SELECT
  Max(l.created) AS created,
  l.unit_id,
  l.activity_id AS Active
  FROM unit_log AS l
  GROUP BY l.unit_id, l.activity_id

What I need and can't seem to get, is the MAX() row per unit_id. I need the unit_id in the query because I am joining to another table. The created column is a timestamp.

Each unit_id will have hundreds of entries. All I'm concerned with is the most recent one per unit. Can someone show me why this isn't working?

Answer 1

SELECT l.unit_id, l.created, l.activity_id
FROM unit_log l
JOIN (SELECT unit_id, MAX(created) AS maxc
      FROM unit_log
      GROUP BY unit_id) m
ON l.unit_id = m.unit_id AND l.created = m.maxc