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

-----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@turnstep.com
PGP Key: 0x14964AC8 200504122153
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

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

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