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/