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. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq