From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL challenge--top 10 for each key value?
Date: Sat, 10 Apr 2004 14:35:38 -0000
Message-ID: <f05c4e4621bb1e444015688bdf608453@biglumber.com> (raw)
In-Reply-To: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: greg@turnstep.com
Subject: Re: SQL challenge--top 10 for each key value?
In-Reply-To: <f05c4e4621bb1e444015688bdf608453@biglumber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox