Date: Sat, 10 Apr 2004 16:46:51 -0700
From: elein <elein@varlena.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: Rod Taylor <pg@rbt.ca>, Greg Stark <gsstark@mit.edu>,
	Jeff Boes <jboes@nexcerpt.com>, pgsql-sql@postgresql.org
Subject: Re: SQL challenge--top 10 for each key value?
Message-ID: <20040410164651.K409@cookie.varlena.com>
Mail-Followup-To: Josh Berkus <josh@agliodbs.com>,
	Rod Taylor <pg@rbt.ca>, Greg Stark <gsstark@mit.edu>,
	Jeff Boes <jboes@nexcerpt.com>, pgsql-sql@postgresql.org
References: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.com>
	<87r7uyf3ki.fsf@stark.xeocode.com>
	<1081481466.56361.808.camel@jester>
	<200404090906.39541.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200404090906.39541.josh@agliodbs.com>
User-Agent: Mutt/1.3.22.1i

Welcome to the real world, Josh.  There are people who
have full time salaried positions soley to attend 
standards meetings.

Note that ROW_NUMBER() really is handy, regardless of the
silly name.  And there was a little python function of mine 
that did it fairly simply, except that you needed to
initialize the counter in the connection before use.

create or replace function pycounter(integer)
returns integer as
'
   if args[0] == 0:
      SD["nextno"] = 1
      return SD["nextno"]
   try:
      SD["nextno"] += 1
   except:
      SD["nextno"] = 1
   return SD["nextno"]
' language 'plpythonu';

And clearly it can be done faster as a little
C function.

elein


On Fri, Apr 09, 2004 at 09:06:39AM -0700, Josh Berkus wrote:
> Rod,
> 
> > Something along the lines of the below would accomplish what you want
> > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of
> > SQL200N)
> 
> Great leaping little gods!   They added something called "row number" to the 
> spec? 
> 
> Boy howdy, folks were right ... the ANSI committee really has completly blown 
> off the relational model completely.   First there was the addition of 
> network-database functions so that IBM could make DB2 look more like a real 
> database, now this ....
> 
> When a standards committee becomes hostage to a handful of vendors, kiss real 
> standards goodbye.
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend