From: Emi Lu <emilu@encs.concordia.ca>
To: pgsql-sql@postgresql.org
Cc: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
Subject: Re: How to realize ROW_NUMBER() in 8.3?
Date: Tue, 03 May 2011 10:53:08 -0400
Message-ID: <4DC016D4.9090608@encs.concordia.ca> (raw)
In-Reply-To: <0E2AEE255CB846CAA0017E7BB54D501C@marktestcr.marktest.pt>
References: <4DAEFF97.9020608@encs.concordia.ca>
<0E2AEE255CB846CAA0017E7BB54D501C@marktestcr.marktest.pt>
Thank you for the info.
I found a simple way:
==========================
[1] create SEQUENCE tmp start 7820;
[2]
insert into desti_table_name
select nextval('tmp'),
c1, c2... ... cN
from t1 left join t2... ... tn
where ... ...
Just for people using 8.3, this is mimic row_number.
Emi
> If your table is not terribly big, you can
> try something like
>
> SELECT a.col1,a.col2, COUNT(*) as row_number
> FROM yourTable a,yourTable b
> WHERE a.col1 >= b.col1 -- I'm assuming col1 is primary key
> GROUP BY a.col1,a.col2
> ORDER BY row_number
>
> This is pure SQL, should work in every version...
>
> Best,
> Oliveiros
>
> ----- Original Message ----- From: "Emi Lu" <emilu@encs.concordia.ca>
> To: <pgsql-sql@postgresql.org>
> Sent: Wednesday, April 20, 2011 4:45 PM
> Subject: [SQL] 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!
>>
>> Ding Ye
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
emilu@encs.concordia.ca +1 514 848-2424 x5884
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: emilu@encs.concordia.ca, oliveiros.cristina@marktest.pt
Subject: Re: How to realize ROW_NUMBER() in 8.3?
In-Reply-To: <4DC016D4.9090608@encs.concordia.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox