Message-ID: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.com>
Date: Thu, 08 Apr 2004 19:55:33 GMT
Lines: 40
From: Jeff Boes <jboes@nexcerpt.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040329
MIME-Version: 1.0
Subject: SQL challenge--top 10 for each key value?
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
To: pgsql-sql@postgresql.org

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