hi to all
I'm having trouble writing a query. Consider the table shown below The PK is formed on columns A and B:
Here's what I need to do. For each distinct value of A, I need to assign a value to column GroupNum in the output. These GroupNum values start at 1, and increase by 1 whenever a new (different) set of B and C values is encountered.
In the table above, (A = 1) and (A = 4) have exactly the same set of ordered B and C values, so they have the same GroupNum (namely, 1).
When (A = 2), the set of B and C values is slightly different, so a new value of GroupNum is assigned (namely, 2).
Finally, (A = 3) and (A = 5) share exactly the same sets of B and C values, so they are assigned a common GroupNum (namely, 3).
The desired output for the table shown above is...
Many thanks for any clues. I just can't seem to get this query to work.
Vicky C
I'm having trouble writing a query. Consider the table shown below The PK is formed on columns A and B:
Code:
[b]
A B C [/b]
1 840 3
1 912 2
1 1010 3
2 840 3
2 912 2
2 1010 [b]2[/b]
3 614 5
3 1260 1
4 840 3
4 912 2
4 1010 3
5 614 5
5 1260 1
... ..... ..
800
Here's what I need to do. For each distinct value of A, I need to assign a value to column GroupNum in the output. These GroupNum values start at 1, and increase by 1 whenever a new (different) set of B and C values is encountered.
In the table above, (A = 1) and (A = 4) have exactly the same set of ordered B and C values, so they have the same GroupNum (namely, 1).
When (A = 2), the set of B and C values is slightly different, so a new value of GroupNum is assigned (namely, 2).
Finally, (A = 3) and (A = 5) share exactly the same sets of B and C values, so they are assigned a common GroupNum (namely, 3).
The desired output for the table shown above is...
Code:
[b]
A GroupNum [/b]
1 1
2 2
3 3
4 1
5 3
....
800
Many thanks for any clues. I just can't seem to get this query to work.
Vicky C