Date: Tue, 6 Apr 2004 23:29:54 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: abhi <abhi@MIT.EDU>
Cc: pgsql-sql@postgresql.org
Subject: Re: Getting the ranks of results from a query
Message-ID: <20040407042954.GA9055@wolff.to>
Mail-Followup-To: abhi <abhi@MIT.EDU>, pgsql-sql@postgresql.org
References: <74D2329E-83F6-11D8-A142-000A95DA7BB2@mit.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <74D2329E-83F6-11D8-A142-000A95DA7BB2@mit.edu>
User-Agent: Mutt/1.5.6i

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 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)