Auto Number

May I have an auto number to reset to one if primary key change?
For example,
Key Field Auto number field
1 1
1 2
2 1
I want to auto number reset to one every time Key field change value.
Any information is great appreciated.
SourisPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Next, a column is not anything like a field. Your attempt at sample
data does not have a single column key as you tried to show -- it has a
two column key. Finally, autonumbering is a concept from a sequential
file system and has no place in an RDBMS. You might want to learn some
of the basics before you try to code in SQL.
Based on the specs you did not post, shoudl the DDL you did not post
look like this?
CREATE TABLE Foobar
(key_1 INTEGER NOT NULL,
key_2 INTEGER NOT NULL,
PRIMARY KEY(key_1, key_2));
Now you will want to add a constraint to keep sequential number in each
key_1 group. In T-SQL this will require a trigger, but in Standard
SQL, you could write:
CHECK
(NOT EXISTS
(SELECT *
FROM Foobar
GROUP BY key_1
HAVING MIN(key_2) - MAX(key_2) +1
<> COUNT(*)))|||On Sat, 29 Jan 2005 17:03:21 -0500, souris wrote:

>May I have an auto number to reset to one if primary key change?
>For example,
>Key Field Auto number field
> 1 1
> 1 2
> 2 1
>
>I want to auto number reset to one every time Key field change value.
>Any information is great appreciated.
>Souris
>
Hi Souris,
I assume that by auto number, you mean IDENTITY? The answer, then, is NO.
The IDENTITY property is intended to result in an incrementing value, to
be used as a meaningless surrogate key. The magic word here is
"meaningless" - are you aware that a series of identity values might
contain gaps? You are trying to put some meaning into the identity values;
you shouldn't.
You don't write why you want to do this. If you want to assign some kind
of ranking that restarts in a new group, the you should not store this
information, but compute it in the appropriate select statements.
See http://www.aspfaq.com/show.asp?id=2427; especially the part after the
last subheading ("Grouping within groups").
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you just want to generate this list of pairs, you can do it with a
improper table structure like this.
CREATE TABLE Foobar
(key_1 INTEGER NOT NULL);
INSERT INTO Foobar (key_1) VALUES (1);
INSERT INTO Foobar (key_1) VALUES (1);
INSERT INTO Foobar (key_1) VALUES (1);
etc.
I will do this in steps so you can see it easier. We need the count of
each key_1 group.
CREATE VIEW FoobarTallies (key_1, tally)
AS
SELECT key_1, COUNT(*)
FROM Foobar
GROUP BY key_1;
The Sequence table is a standard programming trick you can Google. Get
key_2 from the Sequence table
CREATE VIEW FoobarTwoKey (key_1, key_2)
AS
SELECT T1.key_1, S1.seq
FROM FoobarTallies AS T1, Sequence AS S1
WHERE S1.seq <= T1.tally;
Now put both views together:
CREATE VIEW FoobarTwoKey (key_1, key_2)
AS
SELECT F1.key_1, S1.seq
FROM (SELECT key_1, COUNT(*)
FROM Foobar
GROUP BY key_1)
AS F1 (key_1, tally)
CROSS JOIN
(SELECT seq FROM Sequence)
AS S1(seq)
WHERE F1.tally >= S1.seq;
of course this is not a table, not updatable and would seem to be
pretty useless in a valid data model. You can use it to insert into a
properly constructed table to get started, however.