2

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      |
1

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.