Selecting the last record that meets a condition I have a simplified table like this:



id |val |
---|----|
1  |6   |
2  |5   |
3  |4   |
4  |4   |
5  |8   |
6  |9   |
7  |7   |


I want to select the records in a way that where val &lt; 5 the last record where val &gt;= 5 is selected. So the result should look like this:



id |val |
---|----|
1  |6   |
2  |5   |
3  |5   |
4  |5   |
5  |8   |
6  |9   |
7  |7   |


I was wondering if this is possible without defining a function, so I tried:

SELECT
CASE
    WHEN val &lt; 5 THEN LAG(id) OVER (ORDER BY id)
    WHEN val &gt;= 5 THEN id
END id,
CASE
    WHEN val &lt; 5 THEN LAG(val) OVER (ORDER BY id)
    WHEN val &gt;= 5 THEN val
END val
FROM test;


but it only returns:



id |val |
---|----|
1  |6   |
2  |5   |
3  |5   |
4  |4   |
5  |8   |
6  |9   |
7  |7   |


Is is possible to write this query without defining a function? Or would it be better with a function?

EDIT: A combined solution

For the sake of completeness, although it slightly differs from the original problem description, I include that if the answers from @Andriy M and @Hogan are combined then NULL values will be preserved and only values below the threshold will be updated with the last valid value. In some cases it is desirable to keep the NULL values. Thus the query would be like this:

SELECT
    id,
    val,
CASE WHEN val &lt; 5 THEN (SELECT sub.val
                        FROM test AS sub
                        WHERE sub.id &lt;= main.id
                            AND sub.val &gt;= 5
                        ORDER BY id DESC
                        LIMIT 1) 
ELSE val END AS newval
FROM test AS main;


Which returns:



id |val |newval |
---|----|-------|
1  |6   |6      |
2  |5   |5      |
3  |4   |5      |
4  |NULL|NULL   |
5  |8   |8      |
6  |9   |9      |
7  |7   |7      |
Prior answer was wrong as pointed out in the comments -- this works:

SELECT id, val, 
       CASE when val &lt; 5 then (
            select max(val) from t as tsub where tsub.id &lt; t.id) 
       else val end as newval
FROM T


have to use a sub query to find the prior max.