CODEWITHSUNDEEP
mysqlsqlselectmax
k0pernikus
k0pernikus

Reputation: 66430

How to query a row with a unique identifier with their respective max value?

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?

Upvotes: 0

Views: 76

Answers (3)

Jus12
Jus12

Reputation: 18024

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

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

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.

Upvotes: 1

splash58
splash58

Reputation: 26143

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

Upvotes: 0

Related Questions