Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 311F59FB5C1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  6 May 2007 23:03:21 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 60395-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  6 May 2007 23:03:05 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.234])
	by postgresql.org (Postfix) with ESMTP id 534839FB57A
	for <pgsql-sql@postgresql.org>; Sun,  6 May 2007 23:03:17 -0300 (ADT)
Received: by wx-out-0506.google.com with SMTP id i28so1224614wxd
	for <pgsql-sql@postgresql.org>; Sun, 06 May 2007 19:03:15 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=IVeq126CpgrAibLrt3AuSlfIcryVQhgQ7etZOHEXq3cTseJQF8H9yvNpkdl+KYtnB9tANiD2085MMv479IRnUDUtpSa1h3UZTf/449h1GYczamlOhyJdYbq22FiN9dnaU4vnIMu5wKNSn7UzNPdgAz68XGDKp3mFN81tUYuFp74=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=p/1hGVZrqg7kj7gkacsI6iMaB9PT7+CueaeOI/CmmLZ49hUPjPRNyVAtQwvBE48gLEtzkYtz0hvFheBF/IN8Cc1vtBjAbBtF6p/iCstZu+vVhYGy85bWDR2t3bWGCcSXLrCTHTHNKWmrW21EQ6Ljll11dXOGUUzQYi855ZL9poM=
Received: by 10.90.56.5 with SMTP id e5mr648925aga.1178503395360;
	Sun, 06 May 2007 19:03:15 -0700 (PDT)
Received: by 10.90.119.10 with HTTP; Sun, 6 May 2007 19:03:15 -0700 (PDT)
Message-ID: <36af4bed0705061903y711ceb53v79ed176d5f849509@mail.gmail.com>
Date: Mon, 7 May 2007 07:33:15 +0530
From: Robins <tharakan@gmail.com>
Reply-To: robins@pobox.com
To: pgsql-sql@postgresql.org
Subject: Re: ROW_NUMBER alias
Cc: "Stefan Becker" <pgsql@yukonho.de>
In-Reply-To: <200705062041.31192.pgsql@yukonho.de>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_67912_28867334.1178503395321"
References: <36af4bed0705060901s36fed269ye5a8588eaffbd750@mail.gmail.com>
	<200705062041.31192.pgsql@yukonho.de>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/38
X-Sequence-Number: 28319

------=_Part_67912_28867334.1178503395321
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Stefan,

Well that was just an example. That table had a primary key on (id, date).
By bad then, coz I guess I should have clarified that earlier.

But what I meant from the example was that it is trivial for the parser to
automatically put an enveloping SELECT to add a ROW_NUMBER() field to any
user given query.

Regards,
Robins Tharakan

On 5/7/07, Stefan Becker <pgsql@yukonho.de> wrote:
>
> I might be told off by some better SQL-User here on the list -
> still here is my 2 Cents worth....
>
> > I needed ROW_NUMBER() in PostGresql and I did find the 'temporary
> sequence'
> > method as a workaround and i think it at least gets the job done
> relatively
>
> you use:
>
> >     CREATE TEMP SEQUENCE rownum;
> >     SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
> >     FROM (
> >         SELECT a, b, c
> >         FROM table
> >         WHERE table_id  = 973
> >         ORDER BY record_date
> >     ) t;
>
> Doesn't this just return the 973th single record for the current
> sequence order in your table?
>
> I believe:
> SELECT a, b, c FROM table
> offset 973 limit 1
>
> will accomplish the same result.
>
>
> Stefan

------=_Part_67912_28867334.1178503395321
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Stefan,<br><br>Well that was just an example. That table had a primary key on (id, date). By bad then, coz I guess I should have clarified that earlier. <br><br>But what I meant from the example was that it is trivial for the parser to automatically put an enveloping SELECT to add a ROW_NUMBER() field to any user given query.
<br><br>Regards,<br><span style="font-weight: bold;">Robins Tharakan</span><br><br><div><span class="gmail_quote">On 5/7/07, <b class="gmail_sendername">Stefan Becker</b> &lt;<a href="mailto:pgsql@yukonho.de">pgsql@yukonho.de
</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I might be told off by some better SQL-User here on the list -<br>still here is my 2 Cents worth....
<br><br>&gt; I needed ROW_NUMBER() in PostGresql and I did find the &#39;temporary sequence&#39;<br>&gt; method as a workaround and i think it at least gets the job done relatively<br><br>you use:<br><br>&gt;&nbsp;&nbsp;&nbsp;&nbsp; CREATE TEMP SEQUENCE rownum;
<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp; SELECT nextval(&#39;rownum&#39;) AS row_number , t.a, t.b, t.c<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp; FROM (<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT a, b, c<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM table<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE table_id&nbsp;&nbsp;= 973<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER BY record_date
<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp; ) t;<br><br>Doesn&#39;t this just return the 973th single record for the current<br>sequence order in your table?<br><br>I believe:<br> SELECT a, b, c FROM table<br> offset 973 limit 1<br><br>will accomplish the same result.
<br><br><br>Stefan</blockquote></div>

------=_Part_67912_28867334.1178503395321--