select mac.id, mac.ci, mac.loc, mac.last_modified, o.name
from MACHINES mac, OBJS o, OBJS parent
where
mac.id=o.id
and o.parent_id=parent.id
and parent.instance > '0'
and mac.stat='<ACT>'
and substr(o.name, 1, 9) = 'INSTITUTE'
and LAST_MODIFIED in (select max(LAST_MODIFIED) from MACHINES group by ci)
order by ci
from MACHINES mac, OBJS o, OBJS parent
where
mac.id=o.id
and o.parent_id=parent.id
and parent.instance > '0'
and mac.stat='<ACT>'
and substr(o.name, 1, 9) = 'INSTITUTE'
and LAST_MODIFIED in (select max(LAST_MODIFIED) from MACHINES group by ci)
order by ci
select mac.id, mac.ci, mac.loc, mac.last_modified, o.name
from MACHINES mac, OBJS o, OBJS parent
where
mac.id=o.id
and o.parent_id=parent.id
and parent.instance > '0'
and mac.stat='<ACT>'
and substr(o.name, 1, 9) = 'INSTITUTE'
and rowid in (select max(rowid) maxid from MACHINES group by ci)
order by ci
from MACHINES mac, OBJS o, OBJS parent
where
mac.id=o.id
and o.parent_id=parent.id
and parent.instance > '0'
and mac.stat='<ACT>'
and substr(o.name, 1, 9) = 'INSTITUTE'
and mac.rowid in (select max(rowid) maxid from MACHINES group by ci)
order by ci上面第二种写法少了个mac.
from
(
select mac.id, mac.ci, mac.loc, mac.last_modified, o.name ,rank()
over (partition by mac.ci order by mac.last_modified desc) rk
from MACHINES mac, OBJS o, OBJS parent
where mac.id=o.id
and o.parent_id=parent.id
and parent.instance > '0'
and mac.stat='<ACT>'
and substr(o.name, 1, 9) = 'INSTITUTE') t
where rk = 1
select t.id, t.ci, t.loc, t.last_modified, t.name
from
(
select mac.id, mac.ci, mac.loc, mac.last_modified, o.name ,rank()
over (partition by mac.ci order by mac.last_modified desc) rk
from MACHINES mac, OBJS o, OBJS parent
where mac.id=o.id
and o.parent_id=parent.id
and parent.instance > '0'
and mac.stat='<ACT>'
and substr(o.name, 1, 9) = 'INSTITUTE') t
where rk = 1
假设ci1重复3条,last_modified分别为 3,1,2(按大小)
ci2重复2条,last_modified分别为 1,2
那么根据你的第一种写法 max(LAST_MODIFIED) group by ci 为 (2,3) 结果ci1就又出来两条了
根据你的第二种写法 ci1的 max(rowid)对应的last_modified为2不知道我问明白没,我想把ci1里的3和ci2里的2(是它们各自的max(last_modified))对应的记录集取出