Re: SQL challenge--top 10 for each key value?

This solution will be in Monday's edition of
PostgreSQL General Bits (http://www.varlena.com/GeneralBits)
(In other words, if it doesn't do what you mean, let me know now!)

CREATE TYPE topscores AS
(id integer, query integer, checksum char(32), score integer);

CREATE OR REPLACE FUNCTION topscores(integer) RETURNS SETOF topscores AS
'
DECLARE
t topscores%ROWTYPE;
r RECORD;
q RECORD;
n alias for $1;
BEGIN
FOR q IN SELECT distinct query from table70 order by query LOOP
FOR t IN SELECT id , query, checksum, score
FROM table70
where query = q.query
ORDER BY query, score DESC LIMIT n LOOP
RETURN NEXT t;
END LOOP;
END LOOP;
RETURN;
END;
' language 'plpgsql';

select * from topscores(1) ;
select * from topscores(2) ;
select * from topscores(3) ;

On Thu, Apr 08, 2004 at 07:55:33PM +0000, Jeff Boes wrote:
> 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 majordomo(at)postgresql(dot)org