At first glance, I think you'd have to use an iterative solution in the absence of rank(), which is an Oracle analytical function. (That means it's only available in the Personal and Enterprise Editions of the product, not Standard.)
Something like
foreach page_id
select parent_page_id, hits where page_id = x
order by hits desc;
/* rank() would be the relative ordering of the
rows from this query -- first row rank() is 1,
second row rank() is 2, etc. */
endfor
(I believe the rownum is only being used to maintain relative ordering for rows with the same number of hits, according to their ordering in the query prior to sorting/partitioning. That doesn't seem
as useful as sorting on the page name, though. But I could be wrong.)
In any case, if you really want it as a single view, perhaps you
do the PostgreSQL equivalent of wrapping the procedural code in a stored procedure and returning a ref cursor (a result set). But I'm not a PostgreSQL expert. (And every time I try to get into working with ACS long term, life interferes.)
