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@xxxxxxxxxxxxx>
To: "Randal L. Schwartz" <merlyn@xxxxxxxxxxxxxx>
Cc: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Tuesday, May 03, 2005 9:13 PM
Subject: Re: 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@xxxxxxxxxxxxxx)
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
