SQL query


SQL query

Hi all, I am trying to create a new variable depending on already existing values in the table but got stuck.. here is the example

colA colB new_col1 new_col2
jet a 1 1
jet b 1 1
cat c 1 2
cat d 1 2
dog e 2 3
hen f 3 4

I need to create new columns (new_col1 & new_col2) . I am looking to assign a common value for new_col1 if the values in colA are duplicates and values in colB are unique (i.e jet and cat should get the value of 1 in new_col1) and assign a separate value for each duplicate value in colA (i.e . jet should get 1 and cat should get 2 in new_col2).

Could anyone please help me in getting this result? Really appreciate any help on this issue!

Thanks in advance!


Re: SQL query

let's handle these separately to keep it simple.

lets also assume your table is called table one and properly sorted

[font=Courier New]

create table two as
select ColA , row_number as New_ColB
from ( select distinct ColA from one );

** lets count the number of rows per ColA ** ;
create table three as
select ColA , count(*) as CntA
from table one
group by ColA;

create table four as
select ColA ,
case CntA
when 1 then "0"
else "1"
end as New_ColA
from table three ;

** then bring them all together** ;

create table five as
select One.ColA , Four.New_ColA , Two.New_ColB
from one left join two
on One.ColA = Four.ColA

left join two
on One.ColA = Two.ColA ;