PostgreSQL finding nearest parent value of a certain level

Question

*I apologize, my tables displayed correctly when I was writing this question and after publishing the formatting looks off. trying to fix that now
I am trying to write a query in postgresql that would return, for any given child value, the nearest parent value that has reached a certain rank. Currently, I have this query, which displays the entire hierarchical path for any given child value-

WITH RECURSIVE tree AS ( 
   SELECT "ChildDisplayID", 
          "ParentID",
          "Rank",
          1 as level 
   FROM table1
   WHERE "ChildDisplayID" = {{some ChildID}}

   UNION ALL 

   SELECT t1."ChildDisplayID",
          t1."ParentID", 
          t1."Rank",
          t.level + 1
   FROM table1 t1
     JOIN tree t ON t."ParentID" = t1."ChildDisplayID"
)
SELECT *
FROM tree

What I want to do is have in a single row that displays the child ID and the parent ID of the nearest parent whose rank is "Partner". For example, here is the output I am currently getting:

| ChildID | ParentID | Rank | Level |   
|---------|----------|------|-------|  
|   6     |     5    |Associate Manager| 1    |  
|   5     |     4    |Manager| 2    |  
|   4     |     3    |Associate Partner| 3    |  
|   3     |     2    |Partner| 4    |  
|   2     |     1    |Partner| 5    |  
|   1     |         |CEO| 6    |  

Here is the output I want:

|ChildID | Nearest Partner | Rank |
|--------|----------|------|
|6       |3         | Partner |

What is the best way to do this?

Answer 1

You can put a stop condition on the first matching partner in the recursion, then filter the result:

WITH RECURSIVE tree AS ( 
   SELECT "ChildDisplayID" as initialid, "ChildDisplayID", "ParentID", "Rank", 1 as level 
   FROM table1
   WHERE "ChildDisplayID" = {{some ChildID}}
   UNION ALL 
   SELECT t.initialid, t1."ChildDisplayID", t1."ParentID", t1."Rank", t.level + 1
   FROM table1 t1
   INNER JOIN tree t ON t."ParentID" = t1."ChildDisplayID"
   WHERE t."Rank" <> 'Partner'
)
SELECT *
FROM tree
WHERE "Rank" = 'Partner'

It seems like you have a hierarchy where each child has just one parent, so there should be only one match, or no match at all.