postgresql - How to find the last descendant (that matches other criteria) in a linear “ancestor-descendant” relationship -

itemprop = "text">

This question is based on the following question, but with an additional requirement:

Basically, What I need is a postgray-SQL statement that is found in the linear lineage of a previous line; The predecessor-descended relationship that matches the additional criteria

Example:

The contents of the table "RELATIONSHIP_TABLE" here:

  id | ID_Castore | Id_entry | Bool_flag --------------------------------------- 1 | Empty | One | Wrong 2 1 | One | False 3 2 | One | True 4 3 | One | False 5 Empty | B True 6 Empty | C. Wrong 7 6 | C. False   
 Within each particular hierarchy, each record has the same "id_entry" There are 3 different ancestors descended? Relationships in this example:  
  1 1 < - 2 & lt; - 3 & lt; - 4 2. 5 3. 6 & lt; - 7   
 The question shows how to get the last record of each relationship in the example given above:  
  1 4 2. 5 3.7   
 Therefore, I need this time, in the example given above in the example given above by the "id_entry" the last descendant whose "bool_flag" is set to true:  
  1 3 2. 5 3. & lt; Empty result & gt;  
  class = "text" itemprop = "text"> 
 Representation as graphs, trees, chains, etc.  edge lists  usually recursive There are good uses for common table expression - i.e. with  recurring. In  question  
 like:  
  select (ID with walk recurring (ID, id_ancestor, id_entry, bool_flag, id_root, generation) id_ancestor, id_entry, bool_flag, ID, 0 Where RELATIONSHIP_TABLE id_ancestor select zero UNION aLL x.id, x.id_ancestor, x.id_entry, x.bool_flag, walk.id_root, is walk.generation + 1 RELATIONSHIP_TABLE X insider we're running on x.id_ancestor = walk.id) select id_entry, id_root, id (ID, Id_entry, bool_flag, Id_root, generation, select the maximum (in case of bool_flag the generation end) to walk over w The window From the max_enabled_generation w (in the form of id_root rows between PARTITION current row and untold follow)) x Where generation = max_enab led_generation;   
 ... although it seems that there must be a better way to do this in fact how many generations we have gone on each path.  
 If  id_entry  is common to all members of a tree, you can avoid the need to track the  id_root  you can set a  unique   (id_entry, ID)  and  foreign key (id_entry, id_ancestor) reference (id_entry, ID)  Make sure that the order use to create consistent, so:  
  (select with walk recurring (ID, id_ancestor, id_entry, bool_flag, generation) ID, id_ancestor, id_e ntry, where bool_flag, 0 RELATIONSHIP_TABLE id_ancestor select zero UNION ALL is x.id, x.id_ancestor, x.id_entry, x.bool_flag, walk.generation + 1 RELATIONSHIP_TABLE x of x.id_ancestor = walk.id interiors Select id_entry, id from id_entry, bool_flag, select generation, maximum (case when bool_flag then generation end) than walk w over max_enabled_generation w as window (Division between current row and infinite following id_entry ROWS) x ou generation = max_enabled_generation;   
 Since it gives you a table of the final descendant matched with the root parent, then you can just type a regular  where  section now, simply  Filter with Attachments and bool_flag . You can add  any  way point, you can add  where bool_flue  is set to false on bool_flag  instead of the chains that are  Code contains> Recommended  query.  
 SQLFiddle example: