pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to realize ROW_NUMBER() in 8.3?
5+ messages / 4 participants
[nested] [flat]
* How to realize ROW_NUMBER() in 8.3?
@ 2011-04-20 15:45 Emi Lu <emilu@encs.concordia.ca>
2011-04-20 16:06 ` Re: How to realize ROW_NUMBER() in 8.3? Vibhor Kumar <vibhor.kumar@enterprisedb.com>
2011-04-20 16:12 ` Re: How to realize ROW_NUMBER() in 8.3? Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 2 replies; 5+ messages in thread
From: Emi Lu @ 2011-04-20 15:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to realize ROW_NUMBER() in 8.3?
2011-04-20 15:45 How to realize ROW_NUMBER() in 8.3? Emi Lu <emilu@encs.concordia.ca>
@ 2011-04-20 16:06 ` Vibhor Kumar <vibhor.kumar@enterprisedb.com>
1 sibling, 0 replies; 5+ messages in thread
From: Vibhor Kumar @ 2011-04-20 16:06 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
On Apr 20, 2011, at 9:15 PM, Emi Lu wrote:
> 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
Following is a link of deepsz which has a way of implementation of rownum.
http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to realize ROW_NUMBER() in 8.3?
2011-04-20 15:45 How to realize ROW_NUMBER() in 8.3? Emi Lu <emilu@encs.concordia.ca>
@ 2011-04-20 16:12 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-05-03 14:53 ` Re: How to realize ROW_NUMBER() in 8.3? Emi Lu <emilu@encs.concordia.ca>
1 sibling, 1 reply; 5+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2011-04-20 16:12 UTC (permalink / raw)
To: emilu@encs.concordia.ca; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to realize ROW_NUMBER() in 8.3?
2011-04-20 15:45 How to realize ROW_NUMBER() in 8.3? Emi Lu <emilu@encs.concordia.ca>
2011-04-20 16:12 ` Re: How to realize ROW_NUMBER() in 8.3? Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
@ 2011-05-03 14:53 ` Emi Lu <emilu@encs.concordia.ca>
2011-05-04 05:28 ` Re: How to realize ROW_NUMBER() in 8.3? Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 5+ messages in thread
From: Emi Lu @ 2011-05-03 14:53 UTC (permalink / raw)
To: pgsql-sql; +Cc: Oliveiros d'Azevedo Cristina <oliveiros.cristina@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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to realize ROW_NUMBER() in 8.3?
2011-04-20 15:45 How to realize ROW_NUMBER() in 8.3? Emi Lu <emilu@encs.concordia.ca>
2011-04-20 16:12 ` Re: How to realize ROW_NUMBER() in 8.3? Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-05-03 14:53 ` Re: How to realize ROW_NUMBER() in 8.3? Emi Lu <emilu@encs.concordia.ca>
@ 2011-05-04 05:28 ` Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 5+ messages in thread
From: Andreas Kretschmer @ 2011-05-04 05:28 UTC (permalink / raw)
To: pgsql-sql
Emi Lu <emilu@encs.concordia.ca> wrote:
> 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.
Not really... you have to reset the sequence after the select, and i'm
not sure about ordering the result.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-05-04 05:28 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-20 15:45 How to realize ROW_NUMBER() in 8.3? Emi Lu <emilu@encs.concordia.ca>
2011-04-20 16:06 ` Vibhor Kumar <vibhor.kumar@enterprisedb.com>
2011-04-20 16:12 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-05-03 14:53 ` Emi Lu <emilu@encs.concordia.ca>
2011-05-04 05:28 ` Andreas Kretschmer <akretschmer@spamfence.net>
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