Re: [GENERAL] How to realize ROW_NUMBER() in 8.3?
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... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to realize ROW_NUMBER() in 8.3?
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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to realize ROW_NUMBER() in 8.3?
On Thu, Apr 21, 2011 at 9:19 PM, David Fetter da...@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
Re: [GENERAL] How to realize ROW_NUMBER() in 8.3?
On Wed, Apr 20, 2011 at 09:27:18PM +0530, raghu ram wrote: On Wed, Apr 20, 2011 at 9:21 PM, Emi Lu em...@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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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! 丁叶 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to realize ROW_NUMBER() in 8.3?
On Wed, Apr 20, 2011 at 9:21 PM, Emi Lu em...@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 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
