[SQL] SQL challenge--top 10 for each key value?

[Jeff Boes]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]