Identity auto-increment column per/based another c

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

SQL Server 2005 Forums

Transact-SQL (2005)

Identity auto-increment column per/based another c

Author

Topic

ynevet
Starting Member

10 Posts

Posted - 2010-03-03 : 08:22:55

Hello folks,

I have some interesting issue regarding column Identity Increment property.

This my case, I have the following table "Missions_Demands":

ID | MissionID | Demand | DemandNumber
1 43 fff 1
2 23 bbb 1
3 21 aaa 1
4 54 cxs 1
5 54 asa 2
6 54 was 3

The MissionID and DemandNumber columns should be UNIQUE in each row - I

know how to do it.

The DemandNumber column should be with Identity Increment propery - I don't

know hot to do it.

I want to increment the DemandNumber using Identity Increment property, but I want it to be per MissionID column like in my table example(see MissionID 54).

In other words, the MissionID is the only scope for the DemandNumber column incremental and not the table as it is now.
I hope that i'm clear enough and you have a nice solution without writting T-SQL code.

Thank you,

Yair

Yair

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-03-03 : 08:32:23

You cannot use an identity column that way. You will have to build the unique number yourself. Your solution is going to require SQL code

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-03 : 08:34:36

As much i know there is not any facility like identity increment depends on group data of perticular column.
you will have to handle it in stored procedure at the time of insertion.

Vabhav T

ynevet
Starting Member

10 Posts

Posted - 2010-03-03 : 08:37:14

Ok.

Do you have any idea how can I do it using code?

Yair

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-03 : 08:50:43

Yes you can do like below.

DECLARE @MissionID INT
SET @MissionID = 74
DECLARE @Demand VARCHAR(10)
SET @Demand = 'xyz'

DECLARE @DemandNumber INT

SELECT @DemandNumber = (ISNULL(Max(DemandNumber) + 1,1)) FROM Mission_Demands WHERE MissionID = @MissionID

INSERT INTO Mission_Demands VALUES( @MissionID , @Demand, @DemandNumber )

Please let me know if you want something else

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:21:08

quote:
Originally posted by ynevet

Hello folks,

I have some interesting issue regarding column Identity Increment property.

This my case, I have the following table "Missions_Demands":

ID | MissionID | Demand | DemandNumber
1 43 fff 1
2 23 bbb 1
3 21 aaa 1
4 54 cxs 1
5 54 asa 2
6 54 was 3

The MissionID and DemandNumber columns should be UNIQUE in each row - I

know how to do it.

The DemandNumber column should be with Identity Increment propery - I don't

know hot to do it.

I want to increment the DemandNumber using Identity Increment property, but I want it to be per MissionID column like in my table example(see MissionID 54).

In other words, the MissionID is the only scope for the DemandNumber column incremental and not the table as it is now.
I hope that i'm clear enough and you have a nice solution without writting T-SQL code.

Thank you,

Yair

Yair

you need this


SELECT ID , MissionID , Demand,DemandNumber
FROM
(
SELECT ID , MissionID , Demand,ROW_NUMBER() OVER (PARTITION BY MissionID ORDER BY ID) AS DemandNumber
FROM Table
)t

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

ynevet
Starting Member

10 Posts

Posted - 2010-03-03 : 10:07:32

Hi vaibhavktiwari83,

Your solution is nice but not supporting IDENTITY.

If I have 10 demands for MissionID 74, the most recent demand will be numbered #10, but what if tommorow I will delete one of the demands of MissionID 74, lets say demand number 9?

The 10th deamnd will be 9?No!
Because I want to keep on IDENTITY for each demand in the scope of MissionID.

Yair

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources