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.
