Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 9CF559FB6F4
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  6 May 2007 13:30:48 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 09382-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  6 May 2007 13:30:40 -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.235])
	by postgresql.org (Postfix) with ESMTP id 74A759FB64D
	for <pgsql-sql@postgresql.org>; Sun,  6 May 2007 13:30:40 -0300 (ADT)
Received: by wx-out-0506.google.com with SMTP id i28so1148493wxd
	for <pgsql-sql@postgresql.org>; Sun, 06 May 2007 09:30:39 -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:mime-version:content-type;
	b=Z3ZKQRfVyK6i+QFjSZMJ1Mu06p3EZJJKMBYec6zJ2hPWiPWFbPHK0zZvpxYlW5V0ti9ZMhNzXoMiM+0E20WmswLrPRi6VnlQ1oMnjWzgUg9YXg0Y01NfKi2go+nd98wNIodqk/425QrB/MW3jkPJwU/6DG+Z4YHfjWSlLUx4jFQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type;
	b=V+t58FeYz+KriaT7t0HtqrWVfWuD3py9r7Big62n3j9VGXSZhpBCT4cllQSePIEjnEXxF7aAVq9PbkmMhTnTeKQd+BWhMW2GXVO2RwLQTdpeGxauIUzxDHliUpxoeu4/qAWU7LqG0ozoSkETuXAtlDnQBwptpFnBopIdKZzqmx0=
Received: by 10.90.92.7 with SMTP id p7mr4423063agb.1178467300009;
	Sun, 06 May 2007 09:01:40 -0700 (PDT)
Received: by 10.90.119.10 with HTTP; Sun, 6 May 2007 09:01:39 -0700 (PDT)
Message-ID: <36af4bed0705060901s36fed269ye5a8588eaffbd750@mail.gmail.com>
Date: Sun, 6 May 2007 21:31:39 +0530
From: Robins <tharakan@gmail.com>
Reply-To: robins@pobox.com
To: pgsql-sql@postgresql.org
Subject: ROW_NUMBER alias
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_63526_13744566.1178467299967"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/34
X-Sequence-Number: 28315

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

Hi,

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
well, ... so no problems there.

Its just that from a usability point of view, isn't it better that we
provide some kind of an aliasing mechanism here that allows a new user to
(unknowingly but) implicitly use a temporary sequence rather than make him
use SubQuery with a COUNT(*) and a comparison operator (with disastrous
performance) instead ??

So for a new user :

A query such as this :

    SELECT ROW_NUMBER() AS row_number , a, b, c
        FROM table
        WHERE table_id  = 973
        ORDER BY record_date;

is internally interpreted by the planner as :

    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;

   DROP SEQUENCE rownum;


Any ideas ?
(Of what I remember, I think till recently PostgreSql internally replaced
'MAX(x)' queries with a 'ORDER BY x DESC LIMIT 1' implicitly)

--
Robins

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

Hi,<br><br>I needed ROW_NUMBER() in PostGresql and I did find the &#39;temporary sequence&#39; method as a workaround and i think it at least gets the job done relatively well, ... so no problems there. <br><br>Its just that from a usability point of view, isn&#39;t it better that we provide some kind of an aliasing mechanism here that allows a new user to (unknowingly but) implicitly use a temporary sequence rather than make him use SubQuery with a COUNT(*) and a comparison operator (with disastrous performance) instead ??
<br><br>So for a new user :<br><br>A query such as this :<br><br>&nbsp;&nbsp;&nbsp; SELECT ROW_NUMBER() AS row_number , a, b, c<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; FROM table<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHERE table_id&nbsp; = 973<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ORDER BY record_date;<br><br>is internally interpreted by the planner as :
<br><br>&nbsp;&nbsp;&nbsp; CREATE TEMP SEQUENCE rownum;
<br><br>&nbsp;&nbsp;&nbsp; SELECT nextval(&#39;rownum&#39;) AS row_number , t.a, t.b, t.c<br>&nbsp;&nbsp;&nbsp; FROM (<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; SELECT a, b, c<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; FROM table<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHERE table_id&nbsp; = 973<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ORDER BY record_date<br>&nbsp;&nbsp;&nbsp; ) t;<br><br>

&nbsp;&nbsp; DROP SEQUENCE rownum;<br><br>&nbsp;&nbsp;  <br clear="all">Any ideas ?<br>(Of what I remember, I think till recently PostgreSql internally
replaced &#39;MAX(x)&#39; queries with a &#39;ORDER BY x DESC LIMIT 1&#39; implicitly)<br><br>--<br>Robins

------=_Part_63526_13744566.1178467299967--