Re: Determining Rank - Mailing list pgsql-sql

Michael,

That's an excellent solution, but on my table, the explain plan sucks
and the query time is over 3 minutes when implemented.  Is there a
simple way to get a row_num without using a temporary sequence?

Thanks for your help.

-Don


On Fri, 4 Feb 2005 14:02:20 +0900, Michael Glaesemann
<grzm@myrealbox.com> wrote:
> 
> On Feb 4, 2005, at 12:06, Don Drake wrote:
> 
> > I have a query that shows the top N count(*)'s.
> >
> > So it's basically:
> >
> > 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.
> 
> There are a couple of different ways to go about this. One is just to
> append an extra column that's basically a line number, but I find it
> doesn't handle ties very elegantly. The following example uses a
> correlated subquery using HAVING to determine the rank as "the number
> of items that have a total quantity greater than the current item + 1".
> Note that items bar and baz have exactly the same totals and are tied,
> while the rank of bat shows that there are 3 items that have totals
> greater than bat.
> 
> Joe Celko's "SQL for Smarties" has a bunch of things like this in it.
> I've found it quite helpful.
> 
> Hope this helps.
> 
> Michael Glaesemann
> grzm myrealbox com
> 
> create table items (
>      item text not null
>      , qty integer not null
> ) without oids;
> 
> insert into items (item, qty) values ('foo', 1);
> insert into items (item, qty) values ('foo', 2);
> insert into items (item, qty) values ('foo', 1);
> insert into items (item, qty) values ('foo', 3);
> insert into items (item, qty) values ('foo', 3);
> insert into items (item, qty) values ('foo', 20);
> insert into items (item, qty) values ('foo', 1);
> insert into items (item, qty) values ('bar', 3);
> insert into items (item, qty) values ('bar', 1);
> insert into items (item, qty) values ('bar', 3);
> insert into items (item, qty) values ('bar', 13);
> insert into items (item, qty) values ('baz', 2);
> insert into items (item, qty) values ('baz', 4);
> insert into items (item, qty) values ('baz', 14);
> insert into items (item, qty) values ('bat', 3);
> insert into items (item, qty) values ('bat', 4);
> 
> select item, sum(qty) as tot_qty
> from items
> group by item
> order by tot_qty desc;
> 
>   item | tot_qty
> ------+---------
>   foo  |      31
>   bar  |      20
>   baz  |      20
>   bat  |       7
> (4 rows)
> 
> select i1.item
>      , i1.tot_qty
>      , ( select count(*)
>          from (
>              select item
>                  , sum(qty) as tot_qty
>              from items
>              group by item
>              having sum(qty) > i1.tot_qty
>              ) as i2
>          ) + 1 as rank
> from (
>      select item
>          , sum(qty) as tot_qty
>      from items
>      group by item
>      ) as i1
> order by i1.tot_qty desc;
> 
>   item | tot_qty | rank
> ------+---------+------
>   foo  |      31 |    1
>   bar  |      20 |    2
>   baz  |      20 |    2
>   bat  |       7 |    4
> (4 rows)
> 
> 


-- 
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574