Re: getting the ranks of items

How about something like:

CREATE TABLE testrank (
id int,
value varchar
);

insert into testrank values(17,'way');
insert into testrank values(27,'foo');
insert into testrank values(278,'bar');
insert into testrank values(1,'abd');
insert into testrank values(2,'def');

CREATE OR REPLACE FUNCTION ranker(text) RETURNS SETOF RECORD AS $$
my ($query) = @_;

my $rv = spi_exec_query($query);
my $rows = [];
foreach my $rn (0 .. ($rv->{processed})) {
my $row = $rv->{rows}[$rn];
$row->{index} = $rn+1;
push @$rows,$row;
}
return $rows;
$$ language plperl;

select * from ranker('select * from testrank order by value') as t(index
int,id int,value varchar);

1,1,"abc"
2,278,"bar"
3,2,"def"
4,27,"foo"
5,17,"way"

Sorry, the results don't paste in very well, but you get the idea. This
would probably need to be cleaned up a bit, but I think would do something
like what you need.

Sean

----- Original Message -----
From: "Lyubomir Petrov" <lpetrov(at)sysmaster(dot)com>
To: "Randal L. Schwartz" <merlyn(at)stonehenge(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, May 03, 2005 9:13 PM
Subject: Re: [GENERAL] getting the ranks of items

> Randal L. Schwartz wrote:
>
>>I'm probably asking a FAQ, but a few google searches didn't seem
>>to point me in the right place.
>>
>>Is there a simple way with PostgreSQL to assign relative ranks to the
>>result of a query ORDER BY? That is, I want to either have a view
>>that cheaply assigns the ranks, or be able to update a column with the
>>current ranks (yes, I know this latter version is more prone to
>>error).
>>
>>I'm certain there's probably something I can do to laminate an array
>>value to a query result. Am I confused? (Yes!)
>>
>>
>
> Randal,
>
> May be you can use something like this:
>
>
> create sequence seq_tmp;
> select nextval('seq_tmp') as rank, a.id, a.name from (select id, name from
> t order by name desc) a;
> drop sequence seq_tmp;
>
>
> I don't know how cheap will this be (because of the sequence), but
> couldn't find another way. I do not think that we have something like
> Oracle's ROWNUM...
>
>
> Regards,
> Lyubomir Petrov
>
> P.S. I'm sure you can wrap it in plperl stored procedure :)
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>