列出上下层都有版本版序的树(只要固定root版本版序及下层都取最大版本版序)

[cscxxx]

我想列出一个树使用如下SQL语句:
select child,parent,level,version,iteration
from bomlist
start with parent='parentNumber'
connect by prior child=parent
在parent,child没有版本和版序是可以正常使用的
惨的是parent,child有版本版序所以上面的SQL语句会列出所有版本版序的数据
怎么样修改让取得的child只列出最大的版本版序
试过
select child,parent,level,version,iteration
from bomlist
start with parent='parentNumber' version='A' iteration='1'
connect by prior child=parent
只有最上层的parentNumber列出来的是符合版本版序的
请教如何让下层(层数不定)都只列出最大的版本(最大ZZ)及版序(最大65535)

[bw555]

with T as (
select child,parent,
MAX(version) KEEP(DENSE_RANK LAST ORDER BY version,iteration,ROWID) version,
MAX(iteration) KEEP(DENSE_RANK LAST ORDER BY version,iteration,ROWID) iteration
from bomlist
group by parent,child
)
select child,parent,level,version,iteration
from T 
start with parent='parentNumber'
connect by prior child=parent

[cscxxx]

引用 1 楼 bw555 的回复:

with T as (
select child,parent,
MAX(version) KEEP(DENSE_RANK LAST ORDER BY version,iteration,ROWID) version,

感谢版主回覆，虽然我想要取得的是下阶组件或原料的最高版本，说明如下: 原数据: 成品E.1 组件甲A.1 原料一A.3 原料一B.1 原料二C.2 组件甲A.2 原料一A.3 原料一B.1 原料三A.4 组件乙B.4 原料四A.2 原料四A.2 原料五F.1 结果(同一层只留每个组件或原料的最高版本): 成品E.1 组件甲A.2 原料一B.1 原料三A.4 组件乙B.4 原料四A.2 原料四A.2 原料五F.1 但是感谢版主提供的思路，所以我先把表数据整成组件或原料的最高版本版序的临时表T，之后再组上下层:

with T as (
select a.partno,a.constituentpartno,a.version,a.iteration,a.plocation,a.phantom,a.isassembly,constituentpartqty,constituentpartunit,drawing,changenumber,modifier,effectivedate
from purgebomlist a,(
select partno,version,max(iteration) as iteration
from(
select a.partno,b.version,a.iteration
from purgebomlist a,(
select a.partno,b.data as version
from(
select partno,MAX(data2) as version
from (select a.partno,a.version,b.data2 from purgebomlist a,sys_ctl b where b.sys='PVERSION' and b.ctl_id='PART' and ctl_type='PART_VERSION' and a.version=b.data)
group by partno) a,sys_ctl b
where b.sys='PVERSION' and b.ctl_id='PART' and ctl_type='PART_VERSION' and a.version=b.data2) b
where a.partno=b.partno and a.version=b.version
)
group by partno,version
) b
where a.partno=b.partno and a.version=b.version and a.iteration=b.iteration
)
select constituentpartno,partno,version,iteration,level,plocation,phantom,isassembly,constituentpartqty,constituentpartunit,drawing,changenumber,modifier,effectivedate
from T
start with partno='最上层成品' and version='E' and iteration='1'
connect by prior constituentpartno=partno

[cscxxx]

select a.partno,a.version,b.data2 from purgebomlist a,sys_ctl b where b.sys='PVERSION' and b.ctl_id='PART' and ctl_type='PART_VERSION' and a.version=b.data

以上的表sys_ctl是由A到ZZ的一个排序比较，就是把A到ZZ转换成001到702来比对版本大小