-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> How would you go about getting the top N (say, the top 10) for each query?
Assume you have a table "ch" and three sequences 'aa', 'bb', and 'cc'.
(Only 'aa' and 'bb' need to be initially set)
SELECT setval('aa',1,'f');
SELECT setval('bb',1,'f');
SELECT nextval('cc') AS rating,q2 AS query, s2 AS score FROM
(SELECT 0 AS q1, 0 AS s1, NULL AS cs, nextval('aa') AS v1UNION ALL(SELECT *, nextval('aa') AS v1 FROM (SELECT query AS
q1,MAX(score) AS s1, checksum AS cs FROM ch GROUP BY 1,3 ORDER BY 1 ASC, 2 DESC) AS foo)
) AS uno,
((SELECT *, nextval('bb') AS v2 FROM (SELECT query AS q2, MAX(score) AS s2, checksum AS cs FROM ch GROUP BY 1,3 ORDER
BY1 ASC, 2 DESC) AS foo)UNION ALLSELECT NULL AS q2, 0 AS s2, NULL AS cs, nextval('bb') AS v2
) AS dos
WHERE v1 = v2 AND q2 IS NOT NULL
AND ((CASE WHEN q1 != q2 THEN setval('cc',1,'f') ELSE 0 END > 0)OR(CASE WHEN currval('cc')<10 THEN 1 ELSE 0 END >0)
);
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404101029
-----BEGIN PGP SIGNATURE-----
iD8DBQFAeAZ1vJuQZxSWSsgRAqYuAJ9HaYLotPYkyi1U76I9xnvi8AhLTQCfUyJq
+iVdbz5U7HKep89z0kp49U0=
=6+OH
-----END PGP SIGNATURE-----