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
Date: Tue, 6 Apr 2004 23:29:54 -0500
Message-ID: <20040407042954.GA9055@wolff.to> (raw)
In-Reply-To: <74D2329E-83F6-11D8-A142-000A95DA7BB2@mit.edu>
References: <74D2329E-83F6-11D8-A142-000A95DA7BB2@mit.edu>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, abhi@MIT.EDU
Subject: Re: Getting the ranks of results from a query
In-Reply-To: <20040407042954.GA9055@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox