Re: Determining Rank

On Thu, Feb 03, 2005 at 09:06:36PM -0600, Don Drake wrote:

> select some_val, count(*)
> from big_table
> group by some_val
> order by count(*)
> limit 50
>
> Now, I would like to have the rank included in the result set. The
> first row would be 1, followed by 2, etc. all the way to 50.

Maybe use a temporary sequence?

CREATE TEMPORARY SEQUENCE rank_seq;

SELECT nextval('rank_seq') AS rank, *
FROM (SELECT some_val, count(*)
FROM big_table
GROUP BY some_val
ORDER BY count(*)
LIMIT 50) AS s;

DROP SEQUENCE rank_seq;

I don't know if row order is guaranteed to survive a subquery,
however.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/