Select a row in a SQL table only if a value is in the next few rows of the table?

Question

How do I select rows from a SQL table with a specific column value, but only if one of two specific column values are in the next five rows?

For example I have a table that looks like the following:

id | name     | action 
-----------------------
 1 | New York |  1 
 2 | Boston   |  3
 3 | Dallas   |  2
 4 | Boston   |  4
 5 | New York |  2
 6 | Chicago  |  5
 7 | Dallas   |  6

I want to select the rows where action=1, if and only if there is in a row in the next five rows of the table where action = 4 or action = 6. In the above table this would return the first row (id=1, New York), only because of row id=4 (Boston, action=4) but not because of row id=7 (Dallas, action=6)

Answer 1

Use window functions:

select *
from (
    select t.*,
        count(*) filter(where action in (4, 6)) over(order by id rows between 1 following and 5 following) cnt
    from mytable t
) t
where action = 1 and cnt > 0