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/