From: Robins <tharakan@gmail.com>
To: pgsql-sql@postgresql.org
Subject: ROW_NUMBER alias
Date: Sun, 6 May 2007 21:31:39 +0530
Message-ID: <36af4bed0705060901s36fed269ye5a8588eaffbd750@mail.gmail.com> (raw)
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
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: tharakan@gmail.com
Subject: Re: ROW_NUMBER alias
In-Reply-To: <36af4bed0705060901s36fed269ye5a8588eaffbd750@mail.gmail.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