-----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-----