From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL challenge--top 10 for each key value?
In-Reply-To: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.com>
Date: Sat, 10 Apr 2004 14:35:38 -0000
Message-ID: <f05c4e4621bb1e444015688bdf608453@biglumber.com>


-----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 v1
 UNION 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 BY 1 ASC, 2 DESC) AS foo)
 UNION ALL
 SELECT 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-----