CODEWITHSUNDEEP
sql
Tushar Kesarwani
Tushar Kesarwani

Reputation: 77

Calculate Rank Pattern without any order value

My Data is like this -

enter image description here

You can check 3 columns, jil_equipment_id,req_group,operand. Based on these 3 columns i have to generate a new "Patern" Column.

The patern column is a patern and starts from 2 and increases by 1 for each repeated combination of jil_equipment_id,req_group,operand.

The final data will look like this. enter image description here

Please suggest me any possible approach. I am not able to use the RANK()/DENSE_RANK() Function on this.

Upvotes: 0

Views: 33

Answers (2)

Akshey Bhat
Akshey Bhat

Reputation: 8545

select *,Row_Number() over(partition by jil_equipment_id,req_group,operand   order by  jil_equipment_id,req_group,operand) + 1 as pattern
from tab

you can use row_number() function for this.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use row_number(). You want to use the partition by as well:

select t.*,
       (1 + row_number() over (partition by jil_equipment_id, req_group, operand
                               order by content_id
                              )
       ) as pattern
from t;

Upvotes: 1

Related Questions