From: Stefan Becker <pgsql@yukonho.de>
To: pgsql-sql@postgresql.org
Subject: Re: ROW_NUMBER alias
Date: Sun, 6 May 2007 20:41:30 +0200
User-Agent: KMail/1.6.2
References: <36af4bed0705060901s36fed269ye5a8588eaffbd750@mail.gmail.com>
In-Reply-To: <36af4bed0705060901s36fed269ye5a8588eaffbd750@mail.gmail.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Message-Id: <200705062041.31192.pgsql@yukonho.de>

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


-- 
email: stefan@yukonho.de
tel  : +49 (0)6232-497631
http://www.yukonho.de