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?
Date: Sat, 10 Apr 2004 18:54:35 -0700
Message-ID: <20040410185435.A9217@cookie.varlena.com> (raw)
In-Reply-To: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.com>
References: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: elein@varlena.com, jboes@nexcerpt.com
Subject: Re: SQL challenge--top 10 for each key value?
In-Reply-To: <20040410185435.A9217@cookie.varlena.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox