From: Robins <tharakan@gmail.com>
To: pgsql-sql@postgresql.org
Cc: Stefan Becker <pgsql@yukonho.de>
Subject: Re: ROW_NUMBER alias
Date: Mon, 7 May 2007 07:33:15 +0530
Message-ID: <36af4bed0705061903y711ceb53v79ed176d5f849509@mail.gmail.com> (raw)
In-Reply-To: <200705062041.31192.pgsql@yukonho.de>
References: <36af4bed0705060901s36fed269ye5a8588eaffbd750@mail.gmail.com>
<200705062041.31192.pgsql@yukonho.de>
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
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, pgsql@yukonho.de
Subject: Re: ROW_NUMBER alias
In-Reply-To: <36af4bed0705061903y711ceb53v79ed176d5f849509@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