(Postgres) row_number OVER Partition in 8.2

Question

Hi all,

some time ago I happend to resolve a PG related problem with this SO question of mine. Basically it's about using row_number over a partition in 8.4.

Sadly now I have to create the same thing for 8.2 since one of my customers is on 8.2 and needs it desperatly.

What I do know (on 8.4) is the following:

SELECT custId, custName, 'xyz-' || row_number() OVER (PARTITION by custId) 
AS custCode

Basically counting the occurances of custId and assigning custCodes from that. (just an example, to show what I to; of course the query is way more complex)

I figured the solutions provided to the question mentioned above, but did'nt get them working since there's one more hurdle to take. I don't run SQL directly I have to embed it into a xml based config file which creates a certain xml format from the query results. So creating temp stuff or procedures is not really an option.

So here's the question, does anyone of you guys have an idea how to port that solution of mine to PG 8.2?

TIA

K

Answer 1

use depesz solution http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/