Search code examples
sqloracle-databasemariadblag

LAG(...) IGNORE NULLS OVER (ORDER BY....)

I have an ORACLE SQL that has LAG(...) IGNORE NULLS OVER (ORDER BY ...) in the code but getting an error when trying to use it in SQL for a MariaDB. Does anyone know what similar function is for MariaDB?

UPDATE:

The real database I pull from doesn't have the correct step numbers for REWORK steps, so need to tie the step_no to the last row that has MAIN for a step value. As in the table example the desired_results column has step_no 102 for all the REWORK rows, which is from the last MAIN row with 10/10/2018 01:00:03 claim_ts.

Example code:

WITH testTable AS (
     SELECT '10/9/2018 17:22:54' AS claim_ts, 'MAIN' AS step, '100' AS step_no, '100' as desired_results UNION ALL
     SELECT '10/9/2018 20:39:32', 'MAIN', '101', '101'   UNION ALL
     SELECT '10/10/2018 01:00:03', 'MAIN', '102', '102' UNION ALL
     SELECT '10/10/2018 01:01:44', 'REWORK', '5', '102' UNION ALL
     SELECT '10/11/2018 05:55:20', 'REWORK', NULL, '102' UNION ALL
     SELECT '10/11/2018 13:12:11', 'REWORK', '5', '102' UNION ALL
     SELECT '10/11/2018 16:45:00', 'REWORK', NULL, '102' UNION ALL
     SELECT '10/12/2018 03:08:25', 'MAIN', '103', '103'
)
SELECT
claim_ts,
step,
step_no,
desired_results

FROM testTable

Example of the results.

Example table

dbfiddle with code

Solution

  • MariaDB does not support the IGNORE NULLS option. But, we can simulate LAG in MariaDB or MySQL using a correlated subquery, e.g.

    SELECT
    id,
    col1,
    col2,
    (SELECT t2.col1 FROM yourTable t2
     WHERE t2.id < t1.id AND t2.col1 IS NOT NULL
     ORDER BY t2.id DESC LIMIT 1) col1_lag
FROM yourTable t1;

    Demo

    Edit:

    The output you are expecting in your updated question is not really a lag, but we can use similar logic to get what you want:

    SELECT
    claim_ts,
    step,
    step_no,
    desired_results,
    (SELECT t2.step_no FROM testTable t2
     WHERE t2.claim_ts <= t1.claim_ts AND t2.step = 'MAIN'
     ORDER BY t2.claim_ts DESC LIMIT 1) AS actual_results
FROM testTable t1;

    Demo