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: 20040410185435.A9217@cookie.varlena.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

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-2014 The PostgreSQL Global Development Group