CODEWITHSUNDEEP
sqlsql-serverrowrankdense-rank
codetech
codetech

Reputation: 113

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

Upvotes: 0

Views: 96

Answers (2)

Falko
Falko

Reputation: 206

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.

Upvotes: 4

HarshP
HarshP

Reputation: 1

Use this

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

Upvotes: -2

Related Questions