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?

That query returns the max(l.created) for the combination of unit_id and activity_id. Do you really need activity_id? If you remove that you will get only one. — Filipe Silva, Oct 03 '13 at 23:45
Thanks Filipe, actually, that column is the most important one. — NaN, Oct 03 '13 at 23:46
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Barmar, Oct 03 '13 at 23:56

score 3 · Accepted Answer · answered Oct 03 '13 at 23:50

3

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

answered Oct 03 '13 at 23:50

Barmar

741,623
53
500
612

score 0 · Answer 2 · answered Oct 03 '13 at 23:50

Your query should work just fine, as long as there is only one activity_id per unit_id. As it's written, it will give you one row per unit per activity id.

Perhaps you need:

SELECT Max(l.created) AS created, l.unit_id FROM unit_log AS l WHERE l.activity_id = 1 --or whatever indicates active GROUP BY l.unit_id, l.activity_id

How to use group by to get only one row

2 Answers2