Re: Getting the ranks of results from a query

On Thu, Apr 01, 2004 at 11:05:55 -0500,
abhi <abhi(at)MIT(dot)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 member b
ORDER BY age, id;

For example:
bruno=> select * from member;
id | age
----+-----
1 | 10
5 | 20
10 | 5
20 | 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 | 2
20 | 3
1 | 4
5 | 5
(5 rows)