3

In Postgresql 8.2 I want to sequentially number rows. I have the table t at SQL Fiddle:

    c 
   ---
    3
    2

I want this:

    c | i 
   ---+---
    2 | 1
    3 | 2

I tried this:

select *
from
    (select c from t order by c) s
    cross join
    generate_series(1, 2) i

And got:

    c | i 
   ---+---
    2 | 1
    3 | 1
    2 | 2
    3 | 2
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260

2 Answers2

3

The only thing I can think of is a sequence. You could do something like this:

drop sequence if exists row_numbers;
create temporary sequence row_numbers;

select next_val('row_numbers'), dt.c
from (select c from t order by c) as dt;

I'd throw a drop sequence row_numbers in as well but the temporary should take care of that if you forget.

This is a bit cumbersome but you might be able to wrap it in a function to hide some of the ugliness.

Keep in mind that 8.2 is no longer supported but 8.4 is and 8.4 has window functions.

References (8.2 versions):

mu is too short
  • 426,620
  • 70
  • 833
  • 800
2

You can use a "triangular join" as in the following:

select a.c, (select count(*) from t where c <= a.c) as i
from t as a
order by i

This assumes, however, that the values of c are unique, as the "row numbering" scheme is simply a count of rows that are less-than-or-equal to the current row. This can be expanded to included a primary key or other unique column for tie-breaking, as necessary.

Also, there can be some performance implications with joining in this manner.

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • +1 `c` is not unique and I could use a second row which is. Performance is not a problem as it is a one time operation. But I preferred the sequence solution as that is what I was planning before posting and I already had it all in my mind (It is much more complex than my example). – Clodoaldo Neto Oct 26 '12 at 23:29