Seq# of a Group details 1

[victora]

I'm trying to create a temporary table needed for another query. This table has multiple records per group as shown. I want to insert a new field (SeqNo) that basically is the seq# of the records within the group. It starts with 1 and increment by 1 for each same MemNum.

Select MemNum, SeqNo, AffCode
From table1
order by MemNum


MemNum SeqNo AffCode
123 1 456
123 2 457
123 3 452

124 1 522
124 2 325
124 3 225
124 4 337

125 1 252
125 2 335

Thank you in advanced
V

 

[mrdenny]

This has to be done via a cursor. There are several examples in the forums on how to do it.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[victora]

Denny,
Thanks for the heads up. If you can show me the link, it will be much appreciated. Looks like I'm getting no result on my search

TIA

 

[bborissov]

What version of SQL Server? Because in SQL 2005 you could use Ranking functions.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

If you are using SQL 2005, then use the ranking functions like Boris suggests.

If you are using SQL 2000, then take a look at the following code. In the code example, I create a table variable called @Data. This represents your real table and is shown for testing purposes only. In your final query, you will want to replace @Data with your actual table.

The process shown here creates a temp table variable @Temp with an identity column. By using the identity column and a derived table that returns the minimum identity value per memnum, you can create the SeqNo column.

[green]-- Setting up test data[/green]
Declare @Data Table(MemNum int, AffCode int)

Insert Into @Data Values(123, 456)
Insert Into @Data Values(123, 457)
Insert Into @Data Values(123, 452)
Insert Into @Data Values(124, 522)
Insert Into @Data Values(124, 325)
Insert Into @Data Values(124, 225)
Insert Into @Data Values(124, 337)
Insert Into @Data Values(125, 252)
Insert Into @Data Values(125, 335)

[green]-- Query begins here[/green]
Declare @Temp Table(RowId Int Identity(1,1), SeqNo Int, MemNum Int, AffCode int)

Insert Into @Temp(MemNum, AffCode)
Select MemNum, AffCode
From   @Data
Order By MemNum

Update T
Set    SeqNo = RowId - MinRowId + 1
From   @Temp T
       Inner Join (
         Select Min(RowId) As MinRowId, MemNum
         From   @Temp
         Group By MemNum
         ) As A
         On T.MemNum = A.MemNum

Select MemNum, SeqNo, AffCode 
From   @Temp
Order By RowId

You should be able to copy/paste this query in to Query Analyzer and run it as is to see how it works.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[victora]

hi george,(gmmastros)

It so happen that we are still on SQL2000. (sigh!)
But I was able to put your solution into work, create the table I need prior to the big query, and it works like a charm!

If only I can give you 5 stars! you saved my going back and forth from crystal to access to sql.

Thanks everybody!