Thread: not so sequential sequences

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


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

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.

Rhys

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.

rhys.stewart@gmail.com ("Rhys Stewart") writes:
> 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
>
>
> seq    |   parish
> ------------------------
> 1      | Kingston
> 1      | Kingston
> 1      | Kingston
> 1      | Kingston
> 2      | Lucea
> 3      | Morant Bay
> 3      | Morant Bay
> 3      | Morant Bay
> 4      | Port Antonio
> 5      | Savannah-La-Mar
> 5      | Savannah-La-Mar
> 5      | Savannah-La-Mar
>
> 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.

The usual way to do this would be to separate out what clearly is a
dependant table; the seq and parish values could be associated with a
smaller table that increments any time you get a new parish.

The process of doing this sort of separation is called "normalization."
<http://en.wikipedia.org/wiki/Database_normalization>
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://linuxfinances.info/info/internet.html
Rules of the Evil Overlord #32. "I will not fly into a rage and kill a
messenger who brings me bad news  just to illustrate how evil I really
am. Good messengers are hard to come by."
<http://www.eviloverlord.com/>

Rhys,
You could create a sequence, then make the "seq" attribute to your table
have a default value of:
seq integer default nextval('your_sequence')

Then every time an insert is done into your table, the "seq" will
increment.  You alternatively could make your "insert" statement have
for that position, the nextval('your_sequence')

Does that help?

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
>
>
> seq    |   parish
> ------------------------
> 1      | Kingston
> 1      | Kingston
> 1      | Kingston
> 1      | Kingston
> 2      | Lucea
> 3      | Morant Bay
> 3      | Morant Bay
> 3      | Morant Bay
> 4      | Port Antonio
> 5      | Savannah-La-Mar
> 5      | Savannah-La-Mar
> 5      | Savannah-La-Mar
>
> 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.
>
> Rhys
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster



--
Email:    louis.gonzales@linuxlouis.net
WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka