SQL autoincrement value based on column-CodePudding

I have case in which I want same autoincrement value in new created column for same business code I have tried below but I am not getting expected result

select *
    , rank() over (partition by business_code order by ID)
from table

I am getting same same value in ID column for all business code which is not desired result.

My Output

Expected Output

CodePudding user response：

Can you try the the following SQL statement:

SELECT id
      ,business_code
      ,DENSE_RANK() OVER (ORDER BY m) NewColumn
FROM (SELECT id
            ,business_code
            ,MIN(id) OVER (PARTITION BY business_code) m
      FROM myTable) d

An explanation how it works: because of provided data i thought to get the minimum id of each business_code first. And as second step ranking that minimum id by value.

CodePudding user response：

Use this

select row_number() over (order by (select null)), a.*
from Table a;