Skip site navigation (1) Skip section navigation (2)

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

From: elein <elein(at)varlena(dot)com>
To: Jeff Boes <jboes(at)nexcerpt(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: SQL challenge--top 10 for each key value?
Date: 2004-04-11 01:54:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
This solution will be in Monday's edition of
PostgreSQL General Bits (
(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);

   t topscores%ROWTYPE;
   r RECORD;
   q RECORD;
   n alias for $1;
   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;
' 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 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.                       
>            ...Nexcerpt... Extend your Expertise
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

In response to

pgsql-sql by date

Next:From: Greg StarkDate: 2004-04-11 04:38:20
Subject: Re: SQL challenge--top 10 for each key value?
Previous:From: eleinDate: 2004-04-10 23:46:51
Subject: Re: SQL challenge--top 10 for each key value?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group