Auto increment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Auto increment

Author

Topic

joelseverich
Starting Member

34 Posts

Posted - 2011-04-15 : 16:03:56

Hi,
Is there a way to make an autoincrement by group field.
ex

tableA has |Code|NF|

then, NF should autofill like this

code NF
1 1
1 2
1 3
2 1
2 2
1 4
3 1
3 2

and so on.

please help me

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-15 : 16:25:30

I don't know of a way to create an identity column or equivalent in the manner you described. But you could do the following:

1. Create the table with code and an auto_id as columns. Make auto_id an identity column.

2. When you select use row_number() function to get the NF value you want. You could even put the select query into a view to simplify the select command for the clients. So it would be like this:

CREATE TABLE TableA (code INT, auto_id INT NOT NULL IDENTITY(1,1));

And now the select would be:

SELECT code, ROW_NUMBER() OVER (PARTITION BY code ORDER BY auto_id asc) AS NF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-04-17 : 01:54:09

have a look at windowing functions like ROW_NUMBER() if you're SQL 2005 or above

http://msdn.microsoft.com/en-us/library/ms186734.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-17 : 19:23:24

Please post real DDL. Learn to use ISO-11179 rules for the data element names (what kind of code? Postal, disease? Treatment?), avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

CREATE TABLE Foobar
(foo_code INTEGER NOT NULL,
foo_seq INTEGER NOT NULL,
PRIMARY KEY (foo_code, foo_seq));

>> Is there a way to make an auto_increment by group field [sic]... then, NF should auto_fill like this ..<<

Field? Columns are not fields. What you are doing is mimicking a deck of punch cards or a magnetic tape. You know, non-relational things with sequential access? Where all data is materialized? Think RDBMS instead.

CREATE TABLE Foobar
(foo_code INTEGER NOT NULL,
foo_max INTEGER NOT NULL
CHECK (foo_max > 0),
PRIMARY KEY (foo_code, foo_max));

Now use a VIEW to get that list and make sure you never have gaps:

CREATE VIEW Foobar_seq (foo_code, foo_seq)
as
SELECT F.foo_code, S.seq
FROM Series AS S, Foobar AS F
WHERE S.seq <= F.foo_max;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources