Identify Duplicate Rows and assign as "Common" or Original

Database

Identify Duplicate Rows and assign as "Common" or Original

Dear all,

I have a data in table like below...

Part Source_ID
101 Store
101 Direct
102 Store
103 Store
103 Direct
104 Direct
104 Store
105 Store
106 Direct
107 Store
108 Store
108 Direct

 

and I want as below...

Part Source_ID
101 Common
102 Store
103 Common
104 Common
105 Store
106 Direct
107 Store
108 Common

 

in short if Part is duplicate then Identify as "Common" otherwise as Source_ID.

Hope its clear!

I am using Teradata SQL Assistant 14.0

 

Thanks & Regards

Vinay

 

 

 

 


Accepted Solutions
Junior Contributor

Re: Identify Duplicate Rows and assign as "Common" or Original

Assuming that the Part/Source_ID combination is unique:

 

select Part,
   case when count(*) = 1 then min(Source_ID) else 'Common' end
from mytable
group by Part

 

 

1 ACCEPTED SOLUTION
1 REPLY
Junior Contributor

Re: Identify Duplicate Rows and assign as "Common" or Original

Assuming that the Part/Source_ID combination is unique:

 

select Part,
   case when count(*) = 1 then min(Source_ID) else 'Common' end
from mytable
group by Part