From: Jeff Boes <jboes@nexcerpt.com>
To: pgsql-sql@postgresql.org
Subject: SQL challenge--top 10 for each key value?
Date: Thu, 08 Apr 2004 19:55:33 GMT
Message-ID: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.com> (raw)
Offered up for anyone with time on their hands. I fiddled around with
this for half an afternoon, then gave up and did it programmatically in
Perl.
Given a table that looks something like this:
id | INTEGER
query | INTEGER
checksum | char(32)
score | INTEGER
include | BOOLEAN
The table is unique by "id". "Checksum" may be repeated, but I only care
if it is repeated within a given group by "query". ("query" is non-null.)
I can get the top scorer for each "query" row by something like this:
SELECT * FROM (
SELECT DISTINCT ON (checksum) *
FROM my_table
ORDER BY checksum, score DESC)
ORDER BY query;
How would you go about getting the top N (say, the top 10) for each query?
And then, if that's too easy for you--consider a further case where I
want every row for a given "query" that has "include" TRUE, and enough
non-"include" rows to make N. I might end up with more than N rows for a
given value of "query" if there were more than N with "include" set.
I headed off in the direction of groups of SELECTs and UNIONs, and quit
when I got to something like four levels of "SELECT ... AS FOO" ...
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
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: jboes@nexcerpt.com
Subject: Re: SQL challenge--top 10 for each key value?
In-Reply-To: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.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