0

Assume I have table exection_records which has the data:

+----+----------+---------------------+
| id | handle   | finishedAt          |
+----+----------+---------------------+
| 1  | task_foo | 2015-08-16 03:10:33 |
| 2  | task_foo | 2015-08-15 04:00:27 |
| 3  | task_foo | 2015-08-14 02:10:25 |
| 4  | task_bar | 2015-08-17 03:00:25 |
| 5  | task_bar | 2015-08-16 02:01:25 |
| 6  | task_bar | 2015-08-13 06:02:50 |
+----+----------+---------------------+

Now I want to get the row where finishedAt at is at its most recent timestamp for each unique handle, that is:

+----+----------+---------------------+
| id | handle   | finishedAt          |
+----+----------+---------------------+
| 1  | task_foo | 2015-08-16 03:01:33 |
| 4  | task_bar | 2015-08-17 03:00:25 |
+----+----------+---------------------+

I know that there is MAX in MySQL.

I could get the very latest record for each task via:

SELECT *,MAX(finishedAt) FROM db.execution_records where taskHandle = 'task_foo';
SELECT *,MAX(finishedAt) FROM db.execution_records where taskHandle = 'task_bar';

Yet I do not want to issue multiple queries but one, and I do not want to name the handles.

How could I achieve my query?

k0pernikus
  • 60,309
  • 67
  • 216
  • 347
  • 1
    Possible duplicate http://stackoverflow.com/questions/32059558/sql-how-to-select-a-row-having-a-column-with-max-value-group-by – k0pernikus Aug 20 '15 at 14:00
  • 1
    Except that this is MySQL *(which doesn't have `ROW_NUMBER()`)*, and that question is Oracle. – MatBailie Aug 20 '15 at 14:01

3 Answers3

1

In MySQL, the simplest way is to have a sub-query that finds the last finished time for each handle, then join those results back on to your table to pick out the whole row.

SELECT
  execution_records.*
FROM
(
  SELECT
    handle, MAX(finished_at) AS max_finished_at
  FROM
    execution_records
  GROUP BY
    handle
)
  AS summary
INNER JOIN
  execution_records
    ON  execution_records.handle      = summary.handle
    AND execution_records.finished_at = summary.max_finished_at

You can still filter this down to specific handles (rather than all of them) with a simple WHERE clause.

WHERE
  summary.handle IN ('task_foo','task_bah')

The optimiser will then use macro-like-expansion to push that where clause down in to your aggregate query, as well as the outer query.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
0
select * 
  from exection_records 
  join (select handle, max(finishedAt) max 
          from exection_records 
        group by handle) m 
    on exection_records.finishedAt=max

Demo on sqlfiddle

splash58
  • 26,043
  • 3
  • 22
  • 34
0
select id, handle, max(finishedAt) from exection_records group by handle;

Here is the output

create table exection_records (id INT, handle VARCHAR(20), finishedAt BIGINT);


insert into exection_records values(1, 'a', 10);
insert into exection_records values(2, 'a', 20);
insert into exection_records values(3, 'a', 30);
insert into exection_records values(4, 'b', 15);
insert into exection_records values(5, 'b', 25);
insert into exection_records values(6, 'b', 35);

select id, handle, max(finishedAt) from exection_records group by handle;

id  handle  max(finishedAt)
1   a   30
4   b   35

SqlFiddle demo

Jus12
  • 17,824
  • 28
  • 99
  • 157
  • You are selecting `id` but do not include it in the `GROUP BY`. The MySQL documention then says that the `id` returned is arbitrary. You could get `1, a, 30` or you could get `2, a, 30` or you could get `3, a, 30`, there are no guarantees. *(From this page: https://dev.mysql.com/doc/refman/5.1/en/group-by-handling.html `The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.`)* – MatBailie Aug 20 '15 at 14:16