How to realize ROW_NUMBER() in 8.3?

On Wed, Apr 20, 2011 at 9:21 PM, Emi Lu <emilu@encs.concordia.ca> 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

Below link will demonstrates ROW_NUMBER features in pre Postgresql-8.4::

http://www.postgresonline.com/journal/archives/79-Simulating-Row-Number-in-PostgreSQL-Pre-8.4.html

--Raghu Ram

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.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Thu, Apr 21, 2011 at 9:19 PM, David Fetter <david@fetter.org> 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

On Wed, Apr 20, 2011 at 09:27:18PM +0530, raghu ram wrote:

On Wed, Apr 20, 2011 at 9:21 PM, Emi Lu <emilu@encs.concordia.ca> 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

Below link will demonstrates ROW_NUMBER features in pre Postgresql-8.4::

http://www.postgresonline.com/journal/archives/79-Simulating-Row-Number-in-PostgreSQL-Pre-8.4.html

another approach:

http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

On 2011-04-20, 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

You definitely want to skim through the SQL Cookbook
(http://www.oreilly.de/catalog/9780596009762/) for this and many other
things.

Iirc, it suggested a set of CTEs that were joined together and grew
exponentially to get the sequence of numbers, which where finally joined
to the actual query. Pretty elegant and with tailored solutions for
various RDBMS including Postgres.

--
Robert...