Thread: How to realize ROW_NUMBER() in 8.3?
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! 丁叶
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!
丁叶
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 Wed, Apr 20, 2011 at 11:51:25AM -0400, Emi Lu wrote:Your best bet is to upgrade to a modern version of PostgreSQL. While
> 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!
> 丁叶
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...