Getting the ranks of results from a query

From: abhi <abhi(at)MIT(dot)EDU>
To: pgsql-sql(at)postgresql(dot)org
Subject: Getting the ranks of results from a query
Date: 2004-04-01 16:05:55
Message-ID: 74D2329E-83F6-11D8-A142-000A95DA7BB2@mit.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Thanks

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2004-04-01 21:20:37 Array_append does not work with Array variables in PL/pgSQL?
Previous Message malia, sean 2004-04-01 15:27:32 Re: DB question - Merging data from one table to another.