Get furthest parent

Multiple row's of the same table can be related to each other by parent_id. Is there a way to get the furthest parent (with an additional condition, like id > 12) of a given row?

+----+-----------+
| id | parent_id |
+----+-----------+
| 12 |         1 |
+----------------+
| 13 |        12 |
+----------------+
| 14 |        13 |
+----------------+
| 15 |        14 |
+----------------+

Given is id 15
Needed is id 13 (because it is the furthest parent with an id higher than 12)

Is there any reasonable way doing this in 1 statement instead of iterating over and over the closest parent again?

Solution

You can use a recursive CTE to find all the parents of a given id value, and assign a level to them to indicate how far away they are from the original id value. Then it is just a case of finding the id value which has the maximum level and is also greater than 12:

WITH RECURSIVE CTE AS
(
  SELECT id, parent_id, 1 AS level
  FROM data
  WHERE id = 15
  UNION ALL
  SELECT data.id, data.parent_id, CTE.level + 1
  FROM data
  JOIN CTE ON data.id = CTE.parent_id 
  WHERE data.id > 12
)
SELECT id
FROM CTE
WHERE level = (SELECT MAX(level) FROM CTE)

Output

Demo on dbfiddle