Message-ID: <3DCAD1FA.7CB51F7@nsd.ca>
Date: Thu, 07 Nov 2002 15:50:02 -0500
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: Jeff Boes <jboes@nexcerpt.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Quartile (etc) ranking in a SQL statement?
References: <aqeajg$pg2$1@news.hub.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

In a PLPGPSQL script, once you know count(*) try 

execute ''select * from table limit '' || int4( theCount / 4);

If you want numbering, create a sequence and add nextval() to the query.

JLL


Jeff Boes wrote:
> 
> Here's a puzzler:
> 
> Given a query that returns rows ranked by some criteria, how can I write
> another query around it that will give me the (say) first quartile (top
> 25%)?  Another way of putting it is: if I have rows that look like this:
> 
> aaa | 1251
> aba | 1197
> cax | 1042
> ... | ...
> axq |  23
> (142 rows)
> 
> How can I write a query that will return these as
> 
>   1 | aaa | 1251
>   2 | aba | 1197
>   3 | cax | 1042
> ... | ... | ...
> 142 | axq |  23
> 
> --
> Jeff Boes                                      vox 616.226.9550 ext 24
> Database Engineer                                     fax 616.349.9076
> Nexcerpt, Inc.                                 http://www.nexcerpt.com
>            ...Nexcerpt... Extend your Expertise
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly