pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDetermining Rank
5+ messages / 4 participants
[nested] [flat]
* Determining Rank
@ 2005-02-04 03:06 Don Drake <dondrake@gmail.com>
2005-02-04 04:21 ` Re: Determining Rank Michael Fuhr <mike@fuhr.org>
2005-02-04 05:02 ` Re: Determining Rank Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 2 replies; 5+ messages in thread
From: Don Drake @ 2005-02-04 03:06 UTC (permalink / raw)
To: pgsql-sql
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.
I can do this in PHP, but stuffing it into the DB query makes other
things easier.
Any ideas?
Thanks.
-Don
--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining Rank
2005-02-04 03:06 Determining Rank Don Drake <dondrake@gmail.com>
@ 2005-02-04 04:21 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-02-04 04:21 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining Rank
2005-02-04 03:06 Determining Rank Don Drake <dondrake@gmail.com>
@ 2005-02-04 05:02 ` Michael Glaesemann <grzm@myrealbox.com>
2005-02-04 21:18 ` Re: Determining Rank Don Drake <dondrake@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Michael Glaesemann @ 2005-02-04 05:02 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining Rank
2005-02-04 03:06 Determining Rank Don Drake <dondrake@gmail.com>
2005-02-04 05:02 ` Re: Determining Rank Michael Glaesemann <grzm@myrealbox.com>
@ 2005-02-04 21:18 ` Don Drake <dondrake@gmail.com>
2005-02-04 21:36 ` Re: Determining Rank PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 5+ messages in thread
From: Don Drake @ 2005-02-04 21:18 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining Rank
2005-02-04 03:06 Determining Rank Don Drake <dondrake@gmail.com>
2005-02-04 05:02 ` Re: Determining Rank Michael Glaesemann <grzm@myrealbox.com>
2005-02-04 21:18 ` Re: Determining Rank Don Drake <dondrake@gmail.com>
@ 2005-02-04 21:36 ` PFC <lists@boutiquenumerique.com>
0 siblings, 0 replies; 5+ messages in thread
From: PFC @ 2005-02-04 21:36 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; Michael Glaesemann <grzm@myrealbox.com>; +Cc: 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
Make your query a set returning function which iterates over the query
results and returns a number as well ?
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-02-04 21:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-04 03:06 Determining Rank Don Drake <dondrake@gmail.com>
2005-02-04 04:21 ` Michael Fuhr <mike@fuhr.org>
2005-02-04 05:02 ` Michael Glaesemann <grzm@myrealbox.com>
2005-02-04 21:18 ` Don Drake <dondrake@gmail.com>
2005-02-04 21:36 ` PFC <lists@boutiquenumerique.com>
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