How identify the next value based on set criteria

I would like to create a SELECT query to select all names where TestID is 34 but also identify the next name on the list is where the Active row is 0 and the previous Active row is 2, 3 or 4 but not 1 or 0.

I would like to identify these results in a separate "virtual" column called Status where all rows where Active is 2, 3 or 4 is "Done" and the next row after the very last Active 2, 3 or 4 is "Next" every other row after can be identified as just blank.

What kind of logic can used to determine these kinds of results?

CodePudding user response：

/* This CTE is just to provide mock data to test the query */
WITH table_name (`ID`, `TestID`, `Name`, `Active`) AS (
    SELECT 1, 34, 'Jackson', 2 UNION ALL
    SELECT 2, 34, 'Jim', 4 UNION ALL
    SELECT 3, 34, 'Jerry', 3 UNION ALL
    SELECT 4, 34, 'Jackie', 0 UNION ALL
    SELECT 5, 34, 'John', 0 UNION ALL
    SELECT 6, 34, 'Kaleb', 0
)
SELECT
    `Name`,
    `Active`,
    CASE
        /* When current row Active is 2, 3 or 4 then Status is Done */
        WHEN `Active` IN (2, 3, 4) THEN 'Done'
        /* When current row Active is 0 and previous row Active is 2, 3 or 4 then Status is Next */
        WHEN `Active` = 0 AND LAG(`Active`) OVER (ORDER BY `ID`) IN (2, 3, 4) THEN 'Next'
        /* Otherwise Status is an empty string */
        ELSE ''
    END AS `Status`
FROM `table_name`
WHERE `TestID` = 34
ORDER BY `ID` ASC;

Read more about window functions here

CodePudding user response：

It looks like you don't need those blank status rows, If that's the case and you just need to know which one is going to be the next. And to achieve this we need to arrange the data in some order [let's use id for that] The following snippet can help

select names, active,
'Done' as status
from <TableName> 
where TestID = 34 and active in (2,3,4) order by id 
union all 
select * from (
 select names, active,
 'Next' as status
 from <TableName> 
 where TestID = 34 and active in (0,1) order by id limit 1 
) next

However, if blank status rows are also require then we can add one more union to achieve it

union all 
select * from (
 select names, active,
 '' as status
 from <TableName> 
 where TestID = 34 and active in (0,1) order by id limit <aBigNumber> offset 2
) blank

aBigNumber can be 18446744073709551615 (max Of Unsigned BIGINT)

then final SQL will look like

select names, active,
'Done' as status
from <TableName> 
where TestID = 34 and active in (2,3,4) order by id 
union all 
select * from (
 select names, active,
 'Next' as status
 from <TableName> 
 where TestID = 34 and active in (0,1) order by id limit 1 
) next
union all 
select * from (
 select names, active,
 '' as status
 from <TableName> 
 where TestID = 34 and active in (0,1) order by id limit 18446744073709551615 offset 2
) blank

Why SubQuery: Because we can not use limit in between of union