Date: Sat, 10 Apr 2004 18:54:35 -0700
From: elein <elein@varlena.com>
To: Jeff Boes <jboes@nexcerpt.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL challenge--top 10 for each key value?
Message-ID: <20040410185435.A9217@cookie.varlena.com>
Mail-Followup-To: Jeff Boes <jboes@nexcerpt.com>,
	pgsql-sql@postgresql.org
References: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.com>
User-Agent: Mutt/1.3.22.1i

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@postgresql.org