0

SQL Fiddle here http://sqlfiddle.com/#!9/dd34e6/1

There are multiple task entries with different timestamps. I am trying to get the latest entry for each taskId as shown by the desired results.

Schema:

CREATE TABLE TaskStatuses
(
    taskId INT,
    status VARCHAR(255),
    timestamp DATETIME
);

Current result:

1   Complete    1530295000
3   Complete    1530294900
3   Active  1530294100
1   Active  1530294000
4   Complete    1530293900
2   Active  1530293500
1   Paused  1530293000
1   Active  1530292000
4   Active  1530292000

Desired result:

1  Complete  1530295000
3  Complete  1530294900
4  Complete  1530293900
2  Active  1530293500

Solution based on this answer 

SELECT a.taskId, a.status, unix_timestamp(a.timestamp) as unix
FROM TaskStatuses a
INNER JOIN (
    SELECT taskId, MAX(timestamp) timestamp
    FROM TaskStatuses
    GROUP BY taskId
) b ON a.taskId = b.taskId AND a.timestamp = b.timestamp
mgibson
  • 6,103
  • 4
  • 34
  • 49

0 Answers0