Re: select top N entries from several groups (Modified by David Orme)

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: select top N entries from several groups (Modified by David Orme)
Date: 2005-04-13 01:58:15
Message-ID: 3580c8b40ce703e9bac422bc76a6dc0b@biglumber.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> How can I get the largest two values for each group in a single pass?
...
> Fortunately the real number of groups is fixed and
> small (8) so this is tractable.

Depends on your definition of "single pass", but if you know exactly
what the groups are, you can do something like this:

(SELECT DISTINCT gp, val FROM temp WHERE gp='A' ORDER BY val DESC LIMIT 2)
UNION ALL
(SELECT DISTINCT gp, val FROM temp WHERE gp='B' ORDER BY val DESC LIMIT 2)
UNION ALL
(SELECT DISTINCT gp, val FROM temp WHERE gp='C' ORDER BY val DESC LIMIT 2)
UNION ALL
(SELECT DISTINCT gp, val FROM temp WHERE gp='D' ORDER BY val DESC LIMIT 2)
UNION ALL
(SELECT DISTINCT gp, val FROM temp WHERE gp='E' ORDER BY val DESC LIMIT 2)
UNION ALL
(SELECT DISTINCT gp, val FROM temp WHERE gp='F' ORDER BY val DESC LIMIT 2)
UNION ALL
(SELECT DISTINCT gp, val FROM temp WHERE gp='G' ORDER BY val DESC LIMIT 2)
UNION ALL
(SELECT DISTINCT gp, val FROM temp WHERE gp='H' ORDER BY val DESC LIMIT 2)
ORDER BY 1,2 DESC;

Creating an index on gp will help out as well:

CREATE INDEX temp_gp ON temp(gp);
ANALYZE temp;

The above ran on my system with 80,000 rows in 1.3 seconds. (Should be even
faster if you don't need the DISTINCT; which can be removed if every
combination of gp and val is unique).

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200504122153
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCXHxfvJuQZxSWSsgRAnXaAKDK3IGx+7fdZhahk3q3x6Pn+TENXgCgqbbR
Y7ybBfp5yfcA1z8ktgdGrU4=
=5ng9
-----END PGP SIGNATURE-----

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Slavisa Garic 2005-04-13 02:29:53 Many connections lingering
Previous Message Keith Worthington 2005-04-12 18:38:56 Using an SRF with VB6