SQL: how to select distinct values with an offset

Given the following table:

id      value     
------- -------
0       1      
1       6      
2       7      
3       9      
4       2      
5       20     
6       21      
7       3  

I’m wondering if there’s a way to select distinct rows where there’s a difference between (integer) values of (for example) 3, giving a priority to id (low id value = more prioririty). Programmatically, I would iterate over values starting from id=0 and discarding rows where value, compared to my current list, has a difference less than 3. Here follows an example in C to give an idea:

std::vector<int> filtered_values;
for (int i=0; i<values.size();i++)
{
    bool update = true;
    for (int j=0; j<filtered_values.size();j++)
    {
        if (abs(filtered_values[j]-values[i]) < 3) {
            update = false;
            break;
        }
    }

    if (update)
        filtered_values.push_back(values[i]);
}

Is it possible to do this filtering in SQL?

The result, given the initial table, would be:

id      value     
------- -------
0       1      
1       6
3       9      
5       20 

Answer

You can do it with a recursive CTE:

with recursive cte as (
  select id, value from tablename
  where id = 0
  union all
  select t.id, t.value
  from cte c inner join tablename t
  on t.id = (select min(id) from tablename where id > c.id and value - c.value >= 3)
)  
select * from cte 

See the demo.
Results:

| id  | value |
| --- | ----- |
| 0   | 1     |
| 1   | 6     |
| 3   | 9     |
| 5   | 20    |