Re: getting the ranks of items
От | Sean Davis |
---|---|
Тема | Re: getting the ranks of items |
Дата | |
Msg-id | 005d01c5504b$10a168b0$5179f345@WATSON обсуждение исходный текст |
Ответ на | getting the ranks of items (merlyn@stonehenge.com (Randal L. Schwartz)) |
Список | pgsql-general |
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@sysmaster.com> To: "Randal L. Schwartz" <merlyn@stonehenge.com> Cc: <pgsql-general@postgresql.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@postgresql.org) >
В списке pgsql-general по дате отправления: