pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: emilu@encs.concordia.ca
To: pgsql-sql@postgresql.org
Subject: Re: How to realize ROW_NUMBER() in 8.3?
Date: Wed, 20 Apr 2011 17:12:05 +0100
Message-ID: <0E2AEE255CB846CAA0017E7BB54D501C@marktestcr.marktest.pt> (raw)
References: <4DAEFF97.9020608@encs.concordia.ca>

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

view thread (5+ messages)  latest in thread

Message-ID: <0E2AEE255CB846CAA0017E7BB54D501C@marktestcr.marktest.pt>
Permalink:  ../0E2AEE255CB846CAA0017E7BB54D501C@marktestcr.marktest.pt/
Also on:    postgresql.org/message-id/0E2AEE255CB846CAA0017E7BB54D501C@marktestcr.marktest.pt

copy link · copy postgr.es

reply

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: oliveiros.cristina@marktest.pt, emilu@encs.concordia.ca
  Subject: Re: How to realize ROW_NUMBER() in 8.3?
  In-Reply-To: <0E2AEE255CB846CAA0017E7BB54D501C@marktestcr.marktest.pt>

* 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