Get sum of latest 5 per id-CodePudding

So I have a query, and this query has to tell me if the result SUM of the last 5 runs per ID, has a different verdict than 'SUCCESS'. I wrote this query

SELECT 
    SUM(CASE
        WHEN testresults.StatusId != 1 THEN 1
        ELSE 0
    END) AS StatusId
FROM
    testreporting.testresults
WHERE
    TestSuiteCollectionId = 461
        AND TestCaseId IN ('3846', '3846', '3840', '3841', '3842', '3845', '5701', '4015', '4008', '3844', '4012', '5702', '3843', '4014', '3847', '4635', '4295', '4446', '4637', '4296', '4297', '443'
'3846')
GROUP BY TestCaseId, TestSuiteId, TestSuiteCollectionId
ORDER BY id DESC;

I wanted to do a LIMIT 5, but that doesn't make sense, because I limit my results table to 5 then.

How can I make this query get the last 5 results for each TestCaseId, and then do the SUM != 1?

It would work if I took one testcase ID at the time like this

SELECT 
    SUM(CASE
        WHEN testresults.StatusId != 1 THEN 1
        ELSE 0
    END) AS StatusId
FROM
    testreporting.testresults
WHERE
    TestSuiteCollectionId = 461
        AND TestCaseId = ('4296')
GROUP BY TestCaseId, TestSuiteId, TestSuiteCollectionId
ORDER BY id DESC
LIMIT 5;

But that wouldn't be efficient, because I have to run this query many times.

CodePudding user response：

Please check if this work for you. Used ROW_NUMBER ordered by ID DESC to generate row numbers and then filered out the ones that are <= 5 so that should be last five runs.

SELECT 
    TestCaseId, SUM(CASE WHEN StatusId != 1 THEN 1 ELSE 0 END) AS StatusId
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY TestCaseId ORDER BY ID DESC) AS row_number
    FROM testresults
    WHERE TestSuiteCollectionId = 461
      AND TestCaseId = ('4296')
)
WHERE row_number <= 5
GROUP BY TestCaseId

CodePudding user response：

A rather inelegant method that works in very old versions of MariaDB that do not support window functions uses group_concat():

SELECT TestCaseId, TestSuiteId, TestSuiteCollectionId,
       (GROUP_CONCAT(tr.StatusId ORDER BY id DESC) LIKE '1,1,1,1,1%') as StatusId
FROM testreporting.testresults tr
WHERE TestSuiteCollectionId = 461 AND
      TestCaseId IN ('3846', '3846', '3840', '3841', '3842', '3845', '5701', '4015', '4008', '3844', '4012', '5702', '3843', '4014', '3847', '4635', '4295', '4446', '4637', '4296', '4297', '443'
'3846')
GROUP BY TestCaseId, TestSuiteId, TestSuiteCollectionId
ORDER BY id DESC;