create view package_age as
with aging as (
select
P.package, P.version,P.release,P.status,
row_number() over (
	partition by P.package
	order by P.version collate vercmp desc, P.release desc
) as age
from
packages P
left join preserve_packages KEEP
on KEEP.package = P.package and KEEP.version = P.version and KEEP.release = P.release
where P.status in ('updated','removed')
and KEEP.package is null
)
select * from aging
union
select P.package, P.version, P.release, P.status, 0 as age
from packages P where P.status = 'installed'
union
select P.package, P.version, P.release, 'preserved' as status, 0 as age
from preserve_packages P
order by package, version collate vercmp desc, release desc
;