Forum OpenACS Q&A: Porting Oracle's rank() function

Collapse
Posted by Nick Carroll on
Hi,

I've stumbled across a chunk of Oracle sql that I would like to port
to Postgresql.  My problem is that I do not know of a PG equivalent
for Oracle's rank() function.  Well actually the whole view
statement is frustrating me... theres also the partition and rownum
keywords to worry about.  Is there a better way of creating this
view in PG?

create or replace view best_referrers_ranked as
    select page_id, parent_page_id, hits,
          rank() over (partition by page_id
                        order by hits desc, rownum) rk
    from  best_referrers;

Rank() definition: computes the rank of each row returned from a
query with respect to the other rows returned by the query.

Thanks,
Nick.

Collapse
Posted by Daryl Biberdorf on

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.)