traverse to previous rows from current row in a table

I need to traverse to previous 10 rows from current row and check if all rows has codealert_hour . If all rows has alerts then display prev row alert else display null.

image shows sample data.

current row is id=1176.

Now it has to traverse from id 1175 to 1166 and check all alerts. Here all alerts are present (i.e no null alerts) so it should fetch prev row alert (i.e id 1175 alert= 5) in a column of current row.

I tried using lag function but its not working. please help.

Answer

You can do it using LAG and also using self join.

Here is the example using LAG.

SELECT
    T.*,
    CASE
        WHEN COUNT(T.CODEALERT_HOUR) OVER(
            ORDER BY
                ID
            ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING
        ) = 10 THEN LAG(T.CODEALERT_HOUR, 1) OVER(
            ORDER BY
                ID
        )
    END AS PREV_ALERT
FROM
    YOUR_TABLE T;

Here is the example using self join

SELECT
    T1.ID,
    T1.CHANNEL,
    T1.READ_VALUE,
    T1.READ_TIME,
    T1.CODEALERT_HOUR,
    CASE
        WHEN COUNT(1) = 10 THEN MAX(CASE
            WHEN T2.ID = T1.ID - 1 THEN T2.CODEALERT_HOUR
        END)
    END AS PREV_ALERT
FROM
    YOUR_TABLE T1
    JOIN YOUR_TABLE T2 ON ( T2.ID BETWEEN T1.ID - 10 AND T1.ID - 1
                            AND T2.CODEALERT_HOUR IS NOT NULL )
GROUP BY
    T1.ID,
    T1.CHANNEL,
    T1.READ_VALUE,
    T1.READ_TIME,
    T1.CODEALERT_HOUR;

Cheers!!