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)
