Re: not so sequential sequences

[Alexander Staubo <alex@xxxxxxxxxxxxxxx>]

On Oct 17, 2006, at 23:18 , Rhys Stewart wrote:

> Hi all, looking for a method to number a table sequentially, but the
> sequence only increments if the value in a certain column is
> different. as in
[snip]

Normalization could solve your problem and also improve your schema:

-- The parish table
create table parishes (
  id serial primary key,
  name varchar
);

-- Your main table
create table ... (
  ...
  parish_id integer references parishes (id)
);

Now you have the parishes table:

id     |   name
------------------------
1      | Kingston
2      | Lucea
3      | Morant Bay
4      | Port Antonio
5      | Savannah-La-Mar

And your main table:

...    | parish_id
------------------------
...    | 1
...    | 1
...    | 1
...    | 1
...    | 2
...    | 3
...    | 3
...    | 3
...    | 4
...    | 5
...    | 5
...    | 5

As you can see, the parish_id field is now your "magic sequence".

> so i guess i would order by a certain column and then the 'magic
> sequence' would be a column that only increments when the column
> changes.

I'm assuming (perhaps wrongly) here that you don't care about a  
strictly sequential number; for example, a field indicating the  
position of something in a queue or list. I'm also assuming that the  
number has no semantic meaning, which might not fit your use case.

Alexander.