Rock Rock - 4 years ago 64
SQL Question

Add Value column using another column as Key

Hopefully the table itself states the problem. Essentially with the

column on the left, is it possible to add a unique code/value column using
as a hash key/set based on the appearance orders of the types:

Type | Code
ADA | 1
ADA | 1
BIM | 2
BIM | 2
CUR | 3
BIM | 2
DEQ | 4
ADA | 1
... | ...

We can't simply hard-code the conversion as each time there's arbitrary number of

Answer Source

You can use dense_rank():

select type, dense_rank() over (order by type) as code
from t;

However, I would advise you to create another table and to use that:

create table Types as (
    select row_number() over (order by type) as TypeId,
    from t
    group by type;

Then, join that in:

select t.type, tt.TypeId
from t join
     types tt
     on t.type = tt.type;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download