Adding Group Column to a Table

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 2000 Forums

Transact-SQL (2000)

Adding Group Column to a Table

Author

Topic

kris22
Starting Member

35 Posts

Posted - 2008-09-02 : 13:38:41

Hi Guys,
I have a table called TBLTEMP with the following columns and i want to add a Group column for this.

C1 C2 C3
41 te cc
51 te cc
51 te cc
51 te cc
42 te cc
41 te cc
51 te cc
51 te cc
42 te cc
41 te cc
51 te cc
51 te cc
51 te cc
51 te cc
51 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc

so the final result i want is like this. If C1 = 41 then i want to increment the group column. Can you guys help me with the query? appreciate your help
Thx
C1 C2 C3 GRP
41 te cc 0
51 te cc 0
51 te cc 0
51 te cc 0
42 te cc 0
41 te cc 1
51 te cc 1
51 te cc 1
42 te cc 1
41 te cc 2
51 te cc 2
51 te cc 2
51 te cc 2
51 te cc 2
51 te cc 2
51 te cc 2
42 te cc 2
41 te cc 3
51 te cc 3
42 te cc 3

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-02 : 15:10:48

Are there any more columns you are not showing, like a primary key or something?

With the data you provided there is no reliable way to update the rows they way you want in a set based manner.

kris22
Starting Member

35 Posts

Posted - 2008-09-02 : 15:34:24

quote:
Originally posted by Lamprey

Are there any more columns you are not showing, like a primary key or something?

With the data you provided there is no reliable way to update the rows they way you want in a set based manner.

Hi,
there is one id column before the C1 which is a identity column. so it is

ID C1 C2 C3
1 41 te cc
2 51 te cc
3 51 te cc
4 51 te cc
5 42 te cc
6 41 te cc
7 51 te cc
8 51 te cc
9 42 te cc
10 41 te cc
11 51 te cc
12 51 te cc
13 51 te cc
14 51 te cc
15 51 te cc
16 51 te cc
17 42 te cc
18 41 te cc
19 51 te cc
20 42 te cc

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-02 : 17:19:08

Here is one way to do the update:

DECLARE @Yak TABLE(ID INT, C1 INT, GRP INT)

INSERT @Yak
SELECT 1, 41,  NULL 
UNION ALL SELECT 2, 51,  NULL
UNION ALL SELECT 3, 51,  NULL
UNION ALL SELECT 4, 51,  NULL
UNION ALL SELECT 5, 42,  NULL
UNION ALL SELECT 6, 41,  NULL
UNION ALL SELECT 7, 51,  NULL
UNION ALL SELECT 8, 51,  NULL
UNION ALL SELECT 9, 42,  NULL
UNION ALL SELECT 10, 41,  NULL 
UNION ALL SELECT 11, 51,  NULL 
UNION ALL SELECT 12, 51,  NULL
UNION ALL SELECT 13, 51,  NULL
UNION ALL SELECT 14, 51,  NULL
UNION ALL SELECT 15, 51,  NULL
UNION ALL SELECT 16, 51,  NULL
UNION ALL SELECT 17, 42,  NULL
UNION ALL SELECT 18, 41,  NULL 
UNION ALL SELECT 19, 51,  NULL
UNION ALL SELECT 20, 42,  NULL 

UPDATE A
SET GRP = (SELECT COUNT(*) FROM @Yak WHERE C1 = 41 AND ID <= A.ID) - 1
FROM @Yak AS A

SELECT * FROM @Yak

kris22
Starting Member

35 Posts

Posted - 2008-09-02 : 17:35:35

quote:

Originally posted by Lamprey

Here is one way to do the update:

DECLARE @Yak TABLE(ID INT, C1 INT, GRP INT)

INSERT @Yak
SELECT 1, 41,  NULL 
UNION ALL SELECT 2, 51,  NULL
UNION ALL SELECT 3, 51,  NULL
UNION ALL SELECT 4, 51,  NULL
UNION ALL SELECT 5, 42,  NULL
UNION ALL SELECT 6, 41,  NULL
UNION ALL SELECT 7, 51,  NULL
UNION ALL SELECT 8, 51,  NULL
UNION ALL SELECT 9, 42,  NULL
UNION ALL SELECT 10, 41,  NULL 
UNION ALL SELECT 11, 51,  NULL 
UNION ALL SELECT 12, 51,  NULL
UNION ALL SELECT 13, 51,  NULL
UNION ALL SELECT 14, 51,  NULL
UNION ALL SELECT 15, 51,  NULL
UNION ALL SELECT 16, 51,  NULL
UNION ALL SELECT 17, 42,  NULL
UNION ALL SELECT 18, 41,  NULL 
UNION ALL SELECT 19, 51,  NULL
UNION ALL SELECT 20, 42,  NULL 

UPDATE A
SET GRP = (SELECT COUNT(*) FROM @Yak WHERE C1 = 41 AND ID <= A.ID) - 1
FROM @Yak AS A

SELECT * FROM @Yak

Hi,

I just tried this query but it is updating the group column with '3' for all the rows.
I already have a temptable with that data i used the query you gave me. please see the query.

UPDATE tbltemp
SET GRP = (SELECT COUNT(*) FROM tbltemp WHERE C1 = 42 AND ID <= tbltemp.ID) - 1

Is it Right? why it is updating the group column with only '3' appreciate your help
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-02 : 18:09:38

Try changing your query from:
UPDATE tbltemp
SET GRP = (SELECT COUNT(*) FROM tbltemp WHERE C1 = 42 AND ID <= tbltemp.ID) - 1

To:
UPDATE T
SET GRP = (SELECT COUNT(*) FROM tbltemp WHERE C1 = 42 AND ID <= T.ID) - 1
FROM tbltemp AS T

Doing it that way works in my example.

kris22
Starting Member

35 Posts

Posted - 2008-09-03 : 12:14:51

quote:
Originally posted by Lamprey

Try changing your query from:
UPDATE tbltemp
SET GRP = (SELECT COUNT(*) FROM tbltemp WHERE C1 = 42 AND ID <= tbltemp.ID) - 1

To:
UPDATE T
SET GRP = (SELECT COUNT(*) FROM tbltemp WHERE C1 = 42 AND ID <= T.ID) - 1
FROM tbltemp AS T

Doing it that way works in my example.

Thank you very much for your help, it works perfect

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources