Help needed UPDATING a table column 1

[teach314]

hello to all
I have a table that has 4 columns as shown below AT LEFT. The Type column is ASC, and within a given Type, the SubType column is ASC. The 4th column in empty.

I want to UPDATE the 4th column (Set) starting at 1 and increasing by 1 with every new unique combination of Type/SubType. The correctly UPDATEd table is shown AT RIGHT.

tblTEST_1303                            tblTEST_1303 
pk                                      pk 
ID   Type   SubType   Set               ID   Type   SubType   Set
 1     16      8                         1     16      8       1
 2     16      8                         2     16      8       1
 3     16      8                         3     16      8       1
 4     16     10                         4     16     10       2
 5     16     10                         5     16     10       2
 6     24      4                         6     24      4       3
 7     24      5                         7     24      5       4    
 8     42      6                         8     42      6       5 
 9     42      6                         9     42      6       5
10     42     13                        10     42     13       6
11     42     13                        11     42     13       6  
12     42     14                        12     42     14       7

I'm getting lost in the details and would be grateful foe any help.

Teach314




The numbers aren't important here, but the records are properly ORDERed BY A, B1, B2, B3, C1, C2.
The actual query has about 1000 records.

I need help adding a column that increments from 1, but that restarts at each new value of A. In the sample shown, the revised query output should look like...



CODE

A Rank B1 B2 B3 C1 C2
3 1 2 0 0 6 0
3 2 2 0 0 6 1
3 3 2 1 1 4 2
3 4 3 1 0 2 6
5 1 0 0 1 3 2
5 2 0 4 0 9 1
5 3 1 0 1 0 0
Thanks in advance for any assistance.

Teach314

 

[PHV]

Something like this ?

SELECT A.ID, A.Type, A.SubType, Count(*) AS [Set]
FROM tblTEST_1303 AS A INNER JOIN (
SELECT DISTINCT Type, SubType FROM tblTEST_1303) AS C ON 100*A.Type+A.SubType >= 100*C.type+C.SubType
GROUP BY A.ID, A.Type, A.SubType

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[teach314]

hey, thanks PHV. Nice response!