# 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 < 5` the last record where `val >= 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 < 5 THEN LAG(id) OVER (ORDER BY id)
WHEN val >= 5 THEN id
END id,
CASE
WHEN val < 5 THEN LAG(val) OVER (ORDER BY id)
WHEN val >= 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 < 5 THEN (SELECT sub.val
FROM test AS sub
WHERE sub.id <= main.id
AND sub.val >= 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      |
``````

I think LAG() cannot be used here, because with LAG you need to be specific about how many rows back you want to go. (It is 1 by default, but you can specify 3, 10 or any other number. The point, however, is that it must be a specific number.) In your situation, you do not know if the last matching value was on the previous row or on the row before it or even earlier.

So, a different approach would be to find the ID of the last row with the matching value, then look that ID up to get the value for the final output – something like this:

``````SELECT
s.id,
t.val
FROM
(
SELECT
id,
MAX(CASE WHEN val >=5 THEN id END) OVER (ORDER BY id ASC) AS last_id
FROM
test
) AS s
INNER JOIN test AS t ON s.last_id = t.id
ORDER BY
s.id ASC
;
``````

Or you could use a correlated subquery to get the last value that is more than 5 in the subset from the lowest ID to the current ID:

``````SELECT
id,
(
SELECT
sub.val
FROM
test AS sub
WHERE
sub.id <= main.id
AND sub.val >= 5
ORDER BY
id DESC
LIMIT
1
) AS val
FROM
test AS main
ORDER BY
id ASC
;
``````

This would be similar to LAG() but more flexible (and likely less efficient) – a LAG() with a tweak, if you like.

• I accepted your answer, because it allows me to get exactly the last record that meets the condition and this is central for my case. – Balazs Dukai Aug 13 '16 at 10:06

Prior answer was wrong as pointed out in the comments -- this works:

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

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

• I guess, by `LAG(CACULATED_VAL)` you mean `LAG(VAL)`, but that still won't work for many sequential rows with `VAL < 5`. – Andriy M Aug 12 '16 at 19:49
• I see, but the table itself doesn't have a CALCULATED_VAL column, and the one you define in the SELECT list as CALCULATED_VAL cannot be referenced in the same SELECT clause. – Andriy M Aug 12 '16 at 19:53
• @AndriyM - your right, that did not work even if it feels like it should -- I wrote a new answer. :D – Hogan Aug 12 '16 at 20:11
• This would work with the OP's example but logically it doesn't match the requirements. You are retrieving the max(val), but that's not the same as "the last record where val >= 5". Probably you need to use ORDER BY and LIMIT, as in my answer, but maybe there are alternatives that I've overlooked. – Andriy M Aug 12 '16 at 20:33
• @AndriyM - It does meet the requirements -- there is a case that is not covered by the requirements -- and our two cases do different things (null for you max less than 5 for me.) – Hogan Aug 14 '16 at 14:47