Using RANK, or finding another way to do this

[LLLLLLL]

I get some results back from a query

Col 1    Col 2
X        ABC
X        DEF
X        GGH
Y        Blah
Z        Foo
X        Bar
Z        Something
G        Blah

 

I'd like to get the count of the item in Column 1, basically to associate each "instance" of the column 1 value to a 1-based index.

Col 1    Col 2      Col 3
X        ABC        1
X        DEF        2
X        GGH        3
Y        Blah      
Z        Foo        1
X        Bar        4
Z        Something  2
G        Foobar

 

In the case of Y and G, they only appear once in the result set, so I'd like column 3 to be either a 0 or NULL.

 

Ideas on the best way to do this?

[fenway]

A few user variables should do the trick -- update one whenever Col1 changes, increment the other when it doesn't.

[LLLLLLL]

Sorry but that doesn't make sense. There's no "changing" value; what I have shown here is a result set.

 

I've found a workaround: a subselect in the query that will show me the total rows. This is shown below. It will help me since I can just parse the results to determine 1,2,3,etc. for the row. Not really what I wanted, but it will do.

Col 1    Col 2      Col 3
X        ABC        4
X        DEF        4
X        GGH        4
Y        Blah       1
Z        Foo        2
X        Bar        4
Z        Something  2
G        Foobar     1

 

[fenway]

Sorry but that doesn't make sense. There's no "changing" value; what I have shown here is a result set.

Yes there is -- as you check each row in order.

[LLLLLLL]

Again, completely unclear what you're trying to say. But my workaround works just fine.

[fenway]

Again, completely unclear what you're trying to say. But my workaround works just fine.

Then, solved?