Re: How to realize ROW_NUMBER() in 8.3?

[raghu ram <raghuchennuru@xxxxxxxxx>]

On Thu, Apr 21, 2011 at 9:19 PM, David Fetter <david@xxxxxxxxxx> wrote:

On Wed, Apr 20, 2011 at 11:51:25AM -0400, Emi Lu wrote:
> Hello,
>
> ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to
> get row_number
>
> select row_number(), col1, col2...
> FROM Â tableName
>
> Thanks a lot!
> äå

Your best bet is to upgrade to a modern version of PostgreSQL. ÂWhile
you will of course need to do tests with your applications, 9.0 has no
significant backward-incompatibility with 8.3.

Hmm, PostgreSQL 8.3 does not support the ROWNUM feature, however, a possible work around can be achieved by using the LIMIT and OFFSET options.

psql=# SELECT empno FROM emp LIMIT 10

The above query will display the first 10 records.

You can also use the (auto incrementing) SERIAL data type as a ROWNUM column to simulate the ROWNUM feature.

Something like this...

psql=# create table rownumtest(rownum SERIAL, val1 varchar, val2 int4);

psql=# insert into rownumtest(val1,val2) values('abc', '1');

psql=# insert into rownumtest(val1,val2) values('def', '2');

psql=# insert into rownumtest(val1,val2) values('ghi', '3');

psql=# insert into rownumtest(val1,val2) values('jkl', '4');

psql=# select * from rownumtest;

rownum | val1 | val2

--------+------+------

1 | abc | 1

2 | def | 2

3 | ghi | 3

4 | jkl | 4

Hope this helps....

--Raghu Ram