auto number

I have two columns A and B, I want to auto-number B with respect to A
How can it be done?
For eg. I will have data like column A column B
A 01
A 02
A 03 ...
then A1 01
A1 02
if the next value for column A = A1, then value for column B should be 03
and if the next value for columnA = A then the next value for column B shoul
d
be 04
Thank you,
-Meselect id, colid,
(select count(*) from syscolumns as B
where A.id = B.id
and A.colid >= B.colid)
from syscolumns as A
order by 1, 2
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 13:53:02 -0700, Me <Me@.discussions.microsoft.com>
wrote:

>I have two columns A and B, I want to auto-number B with respect to A
>How can it be done?
>For eg. I will have data like column A column B
> A 01
> A 02
> A 03 ...
> then A1 01
> A1 02
>if the next value for column A = A1, then value for column B should be 03
>and if the next value for columnA = A then the next value for column B shou
ld
>be 04
>Thank you,
>-Me|||Some examples here.
http://www.aspfaq.com/2427
"Me" <Me@.discussions.microsoft.com> wrote in message
news:4D0AF582-BAD2-49B1-B421-4D22ACEEFE71@.microsoft.com...
>I have two columns A and B, I want to auto-number B with respect to A
> How can it be done?
> For eg. I will have data like column A column B
> A 01
> A 02
> A 03 ...
> then A1 01
> A1 02
> if the next value for column A = A1, then value for column B should be 03
> and if the next value for columnA = A then the next value for column B
> should
> be 04
> Thank you,
> -Me
>