[GENERAL] Ranking values within a query (pseudo-ROWNUM)

[Jeff Boes]

I hope this helps someone else ... I had struggled some time ago with 
attempts to get a rank of values query to work, but then I gave up and 
set it aside. I had another reason to attack it, and in between then and 
now I learned how to return "setof" values from a function, as well as 
how to construct "dynamic" queries inside a function.

Returning the top 10 values from a query is no big deal:
  create table my_table (field1 integer, field2 integer, field3 text);
  ...
  select * from my_table order by field1 limit 10;
If you want the top value for each value of field1, a DISTINCT works:
  select distinct on (field1) * from my_table
  order by field1, field2 desc;
(i.e., the row with the biggest field2 value for each set of rows with 
the same field1 value).

However, if you want the top *N* values, it gets a lot trickier. 
PostgreSQL lacks the concept of ROWNUM, which would let you filter in 
just those rows in the top 5, for example.

Here's what I came up with; I've not applied this to more than a couple 
of test cases, so there may very well be flaws in this approach.

create table rank_of_values(rank_of integer, the_value integer);
(The table is necessary so that our functions can return a rowtype. 
Someday PostgreSQL may have a way to add a rowtype to the database 
without a corresponding table.)

Now there are two functions, with the same name (and Pg isn't confused, 
because they have different numbers of arguments).

The first function is a straightforward ranking of values. Given a 
column name, and a "FROM" clause, it returns a set of rows with the 
column value and rank (tied values do NOT have the same rank).

CREATE FUNCTION fn_rank_values(TEXT,TEXT)
RETURNS setof rank_of_values AS '
    DECLARE
        t RECORD;
        r rank_of_values%ROWTYPE;
        curs REFCURSOR;
        col ALIAS FOR $1;
        stmt ALIAS FOR $2;
        rank INTEGER;
    BEGIN
        OPEN curs FOR EXECUTE 'SELECT "' || col || '" AS
       "the_value" ' || stmt;
        rank := 1;
        LOOP
            FETCH curs INTO t;
            EXIT WHEN NOT FOUND;
            r.rank_of = rank;
            r.the_value = t.the_value;
            RETURN next r;
            rank := 1 + rank;
        END LOOP;
        CLOSE curs;
        RETURN;
    END;
' LANGUAGE 'plpgsql' STABLE;
The second function is the "rank-within-group" facility. Given a 
value-column, a group-column, and a "FROM" clause, it returns the 
original value, and its rank within the group formed by identical values 
of the group-column.

CREATE FUNCTION fn_rank_values(TEXT,TEXT,TEXT)
RETURNS setof rank_of_values AS '
    DECLARE
        t RECORD;
        r rank_of_values%ROWTYPE;
        curs REFCURSOR;
        col ALIAS FOR $1;
        grp ALIAS FOR $2;
        clause ALIAS FOR $3;
        rank INTEGER;
        curr_grp INTEGER;
        stmt TEXT;
    BEGIN
        stmt := 'SELECT "' || col || '" AS "the_value", "' || grp
            || '" AS "the_group" ' || clause;
        OPEN curs FOR EXECUTE stmt;
        rank := 1;
        LOOP
            FETCH curs INTO t;
            EXIT WHEN NOT FOUND;
            IF curr_grp IS NULL
            THEN
                curr_grp = t.the_group;
            ELSIF curr_grp != t.the_group
            THEN
                curr_grp = t.the_group;
                rank = 1;
            END IF;
            r.rank_of = rank;
            r.the_value = t.the_value;
            RETURN next r;
            rank := 1 + rank;
        END LOOP;
        CLOSE curs;
        RETURN;
    END;
' LANGUAGE 'plpgsql' STABLE;
Now you can do things like this:
select rank_of, the_value from fn_rank_values('field1','from mytable 
order by field1') order by rank_of;

which will give you the rank number and original value of each row.
The second function is more interesting, because that's where you get 
the ability to do "top N by X" kinds of queries. For instance,

select field1, field2, field3 from my_table
join fn_rank_values('field1','field2','from my_table order by field1, 
field2') on (field1=the_value)
where rank_of <= 5 order by field2, rank_of;

--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html