Re: getting the ranks of items - Mailing list pgsql-general

From Lyubomir Petrov
Subject Re: getting the ranks of items
Date
Msg-id 427821D7.7070302@sysmaster.com
Whole thread Raw
In response to getting the ranks of items  (merlyn@stonehenge.com (Randal L. Schwartz))
List pgsql-general
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 :)




pgsql-general by date:

Previous
From: merlyn@stonehenge.com (Randal L. Schwartz)
Date:
Subject: Re: getting the ranks of items
Next
From: "Sean Davis"
Date:
Subject: Re: getting the ranks of items