Re: Getting the ranks of results from a query

On Thu, Apr 01, 2004 at 11:05:55 -0500, abhi <abhi@MIT.EDU> wrote:
> I have a query of the form
> 
>     select id from member order by age;
> 
> id
> -----
>  431
>   93
>  202
>  467
>  300
> 
> In addition to the id, I would like the get the rank of the row--
> in other words:
> 
>  id  | rank
> -----+-----------
>  431 | 1
>   93 | 2
>  202 | 3
>  467 | 4
>  300 | 5
> 
> 
> How do I do this with postgres?  In the past, I have used something like
> 
> 
>     select id, identity(int, 1,1) from member order by age;
> 
> 
> is there a postgres equivalent?

Note this is going to be slow. And that it relies on ID being unique.

SELECT id, (SELECT count(*) FROM member a WHERE a.age < b.age OR   (a.age = b.age AND a.id <= b.id)) AS rank FROM
memberb ORDER BY age, id;
 

For example:
bruno=> select * from member;id | age
----+----- 1 |  10 5 |  2010 |   520 |   8 9 |   8
(5 rows)

bruno=> SELECT id, (SELECT count(*) FROM member a WHERE a.age < b.age OR
bruno(>     (a.age = b.age AND a.id <= b.id)) AS rank
bruno->   FROM member b
bruno->   ORDER BY age, id;id | rank
----+------10 |    1 9 |    220 |    3 1 |    4 5 |    5
(5 rows)