Oracle analytic function - use FIRST_VALUE to remove unwanted rows

Question

I believe the Oracle function FIRST_VALUE is what I need to use based on these two questions: SQL - How to select a row with a column with maximum value
Oracle: write record with maximum date

I have 3 tables that represent people related to organizations. Each organization can have a parent org, where ORG.PARENT is the foreign key of ORG.ID (therefore, the table refers to itself). A person can be associated with several groups.

FACE

ID NAME ---------- 1 Bob 

ORG

 ID NAME PARENT ------------------------ 1 A (null) 2 A-1 1 3 A-2 1 4 A-3 1 5 A-1-a 2 6 A-1-b 2 7 A-2-a 3 8 A-2-b 3 

PERSON_TO_ORG

 PERSON_ID ORG_ID ----------------- 1 1 1 3 

I want to list the groups the person is associated with, so I used this query:

 SELECT NAME, ID, sys_connect_by_path(NAME, '/') AS path FROM org START WITH ID IN (SELECT org_id FROM person_to_org WHERE person_id=1) connect by prior org.ID = org.parent; 

... which gives me:

 NAME ID PATH ------------------ A-2 3 /A-2 A-2-a 8 /A-2/A-2-a A-2-b 9 /A-2/A-2-b A 1 /A A-1 2 /A/A-1 A-1-a 5 /A/A-1/A-1-a A-1-b 6 /A/A-1/A-1-b A-2 3 /A/A-2 A-2-a 8 /A/A-2/A-2-a A-2-b 9 /A/A-2/A-2-b A-3 4 /A/A-3 

Note that A-2 appears twice, as it should be. However, I do not want the group to appear twice. I want the group to be displayed only at the lowest level in the tree, that is, at the highest level. This is how I tried to use FIRST_VALUE with no luck - I still get A-2 (and others) appearing twice:

 SELECT id, name, path, first_value(lev) OVER ( PARTITION BY ID,NAME, path ORDER BY lev DESC ) AS max_lev FROM (SELECT NAME, ID, sys_connect_by_path(NAME, '/') AS path, LEVEL as lev FROM org START WITH ID IN (SELECT org_id FROM person_to_org WHERE person_id=1) connect by prior org.ID = org.parent); 

This is similar to the FIRST_VALUE example in Pro Oracle SQL, but I cannot get it to work no matter how I configure the parameters.

How can I only return rows in which the given group has the highest level value (i.e. the most remote in the tree)?

Answer 1

As one of the streams you refer to also says, analysts are not the most efficient way to go here: you need to fill in the population to filter out duplicates.

 SQL> SELECT id 2 , max(name) keep (dense_rank last order by lev) name 3 , max(path) keep (dense_rank last order by lev) path 4 FROM ( SELECT NAME 5 , ID 6 , sys_connect_by_path(NAME, '/') AS path 7 , LEVEL as lev 8 FROM org 9 START WITH ID IN (SELECT org_id FROM person_to_org WHERE person_id=1) 10 connect by prior org.ID = org.parent 11 ) 12 group by id 13 / ID NAME PATH ---------- ----- -------------------- 1 A /A 2 A-1 /A/A-1 3 A-2 /A/A-2 4 A-3 /A/A-3 5 A-1-a /A/A-1/A-1-a 6 A-1-b /A/A-1/A-1-b 7 A-2-a /A/A-2/A-2-a 8 A-2-b /A/A-2/A-2-b 8 rows selected. 

Yours faithfully,
Rob

PS: Here is more information about the LAST aggregation function: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions071.htm#sthref1495

Answer 2

How about this (untested)

 SELECT SELECT id, name, path FROM ( SELECT id, name, path, row_number() over (partition by id,name order by lev desc) as rn FROM ( SELECT NAME, ID, sys_connect_by_path(NAME, '/') AS path, LEVEL as lev FROM org START WITH ID IN (SELECT org_id FROM person_to_org WHERE person_id=1) connect by prior org.ID = org.parent ) ) where rn = 1 

Answer 3

You should separate only OVER (PARTITION BY ID,NAME ORDER BY lev DESC) not ID,NAME, path

Edit: And probably you want first_value(path) , not first_value(lev)