How to Create and Insert Unique Id's Based on another column values in single table?

Question

I have data like this

Id   Revision_Id        A_Id    A_Name   A_Info
================================================
1           0           111     A1      A_test
2           1           111     A1      A1_info
3           0           222     B1      B1
4           1           222     B1      B1 IINFO
5           2           222     B1      B1 TEST
6           0           333     C1      C1
7           1           333     C1      C1 INFO

I need to create table with normal id with auto increment as one column, but I need to add another column Revison_Id with increment values starts from 0, 1, 2 and so on, if the table already have record with A_ID data..

As per my data, if my table don't have any same A_ID value, then my Revision_ID should starts with 0, so my insert need to be as (id(auto increment),0,111,a1,a1 info) like that.

likewise, while insert, if my table already have record in A_Id my Revision_Id needs to count and increase based on that A_id. So to create and insert. How to achieve it?

Answer 1

com/users/3611669/guidog - first of all , thank you so much for answer, I tried your answer, but don't know ,whats wrong, its not working exactly as per expectation, finally , I tried different methods ,and found out this answer bro/sis...

CREATE PROCEDURE SP_INSERT_WITH_REVISIONID       
(@Id int       
)       
AS    
BEGIN
DECLARE @RevisionId int
If EXISTS(SELECT  * FROM TBL_A WHERE A_Id=@Id)
 
BEGIN 
SET @RevisionId = ISNULL((        
      SELECT MAX(Revision_Id) + 1        
      FROM TBL_A  where  A_Id=@Id       
   ), 0) 
INSERT INTO TBL_A
select @RevisionId,
       A.A_ID,
       A.A_Name,
       A.A_Info  from TBL_DATA A where A_Id=@Id
       
END
ELSE
BEGIN
INSERT INTO TBL_A
select 0,
       A.A_ID,
       A.A_Name,
       A.A_Info  from TBL_DATA A where A_Id=@Id
       END     
       END

and My id is auto increment one. This one finally fixed my issue, I am sharing here ,so that ,it may help someone in future. Do let me know if there is any concern.

Answer 2

So if I understand you correct you have a table with all the information in your sample data, except for the column Revision_ID,
and your goal is to create that column ?

If that is correct you can use row_number() like this

select d.id,
       d.a_id,
       row_number() over (partition by d.a_id order by d.a_id) - 1 as Revision_ID,
       d.a_name,
       d.a_info
from   data d

See this DBFiddle

If that is not what you are after, please elaborate more what you need