Where future rows don't exist

Question

I have the following table

CREATE TABLE TaskJournal (
  TaskJournalId INT NOT NULL AUTO_INCREMENT,
  TaskId INT NOT NULL,
  TaskStatus TINYINT NOT NULL,
  TaskStart INT NOT NULL,
  TaskEnd INT NULL,
  TaskInfo TEXT,
  PRIMARY KEY (TaskJournalId)
) ENGINE = InnoDB;

INSERT INTO TaskJournal (TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo) 
VALUES (2, 1, 1533660028, 1533660500, "No issues present"),
(3, 3, 1533660505, 1533660506, "Exception found: Line 95"),
(2, 1, 1533660015, 1533660018, "No issues present"),
(2, 3, 1533660509, 1533660512, "Exception found: Line 95"),
(2, 1, 1533660515, 1533660530, "No issues present");

The problem is this, I want to gather a list of errors but only if at a future time there is no resolution.

Ideally I'd get something like this back

| TaskJournalId | TaskId | TaskStatus | TaskStart  | TaskEnd    | TaskInfo          |
| 3             | 2      | 1          | 1533660015 | 1533660018 | No issues present |

Only because in the future there isn't some TaskId that has the exception present.

So I thought I could do this via group of sub queries something like as follows:

SELECT * 
FROM TaskJournal 
WHERE TaskId = 2 
GROUP BY TaskId, TaskStatus 
HAVING TaskStatus=3 
ORDER BY TaskStart DESC

But the problem with this is that it returns both rows 4 and 2 (chronological descending order). But at some later point there appears to have been a resolution to TaskId=2 but not for TaskId=3.

Any ideas would be helpful.

score 1 · Answer 1 · answered Aug 07 '18 at 18:13

1

MySQL-5.5

Bummer.

It appears Analytical Functions are not supported in v5.5

If it did, the solution would be something like:

select TaskJournalId, TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo
from (
  select  TaskJournalId, TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo
    ,row_number() over (partition by TaskJournalId,TaskId order by TaskStart DESC) rn
  from TaskJournal
)
where TaskStatus=3 and rn=1;

My Answer

Upgrade your database.

answered Aug 07 '18 at 18:13

Michael Kutz

4,829
1
9
14

Actually I did partially go with this solution, I am having the db servers upgraded to accommodate the extra functionality that I may require. – Mark D Aug 09 '18 at 14:32

Kondybas · Answer 2 · 2018-08-07T18:57:50.607

Look like you want to get the list of issues for each TaskId that follows the last occurence of the No issues present or exactly the string No issues present if that list is empty for certain TaskId. The special case if there is no single No issues present but there is some errors for certain TaskId.

SELECT IFNULL( q.TaskJournalId, x.TaskJournalId ) AS TaskJournalId
       IFNULL( q.TaskId, x.TaskId )               AS TaskId
       IFNULL( q.TaskStatus, x.TaskStatus )       AS TaskStatus
       IFNULL( q.TaskStart, x.TaskStart )         AS TaskStart 
       IFNULL( q.TaskEnd, x.TaskEnd )             AS TaskEnd
       IFNULL( q.TaskInfo, x.TaskInfo )           AS TaskInfo

  FROM ( SELECT DISTINCT TaskId 
           FROM TaskJournal 
       ) AS z

  LEFT JOIN ( SELECT TaskId, MAX(TaskStart) AS maxTS
                FROM TaskJournal 
               WHERE TaskInfo = 'No issues present'
               GROUP BY TaskId
            ) AS w
         ON w.TaskId = z.TaskId

  JOIN TaskJournal  AS x 
         ON x.TaskId = z.TaskId
        AND x.TaskStart = w.maxTS

  LEFT JOIN TaskJournal AS q 
         ON q.TaskId = z.TaskId
        AND ( q.TaskStart > w.maxTS OR w.maxTS IS NULL ) 
;

score 0 · Accepted Answer · answered Aug 09 '18 at 14:28

Turns out this wasn't nearly as complicated as I thought, at least when I took a step back and looked at the problem again. Below is the query I came up with (and a few minor flourishes to enhance the output).

  SELECT TaskJournalId, 
         TaskId, 
         TaskStatus, 
         FROM_UNIXTIME(MAX(TaskStart)) AS LastRun, 
         FROM_UNIXTIME(TaskEnd) AS TaskEnd,
         CASE
             WHEN (LENGTH(MAX(TaskInfo)) = LENGTH('No issues present')) THEN 'OK'
             ELSE CONCAT('FAIL:', SUBSTR(MAX(TaskInfo), 17, LENGTH(MAX(TaskInfo)))) 
         END AS TaskInfo
    FROM TaskJournal
GROUP BY TaskId
ORDER BY TaskId;

Where future rows don't exist

3 Answers3

MySQL-5.5

My Answer