sqlsql-serverrowrankdense-rank

SQL autoincrement value based on column

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

Id businesscode NewColumn 1 eng 1 2 mkr 1 3 eng 2 4 fin 1 5 mkr 2

Expected Output

Id businesscode NewColumn 1 eng 1 2 mkr 2 3 eng 1 4 fin 3 5 mkr 2

Solution

  • 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.