pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting the ranks of results from a query
2+ messages / 2 participants
[nested] [flat]
* Getting the ranks of results from a query
@ 2004-04-01 16:05 abhi <abhi@MIT.EDU>
2004-04-07 04:29 ` Re: Getting the ranks of results from a query Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 2+ messages in thread
From: abhi @ 2004-04-01 16:05 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Getting the ranks of results from a query
2004-04-01 16:05 Getting the ranks of results from a query abhi <abhi@MIT.EDU>
@ 2004-04-07 04:29 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2004-04-07 04:29 UTC (permalink / raw)
To: abhi <abhi@MIT.EDU>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-04-07 04:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-01 16:05 Getting the ranks of results from a query abhi <abhi@MIT.EDU>
2004-04-07 04:29 ` Bruno Wolff III <bruno@wolff.to>
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