CODEWITHSUNDEEP
sqlteradata
John Smith
John Smith

Reputation: 2886

Unique Counts in Teradata per column

I have a dataset that consists of the following

ID     SubID
1        1
1        2
2        1
3        1

I want a third column that basically flags the first ID and any subsequent IDs which are the same are ignored In essence someone (end user) wants to keep the details but also sum up the unique IDs in excel

ID     SubID  ident
1        1      1
1        2      0 
2        1      1
3        1      1

I suspect i can use a rank function here but would appreciate any hints

Upvotes: 0

Views: 889

Answers (2)

dnoeth
dnoeth

Reputation: 60462

No RANK, but a ROW_NUMBER:

select ID, SubID, 
   case when ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SubID) = 1
        then 1 
        else 0
   end
from tab

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do this using row_number():

select t.*,
       (case when row_number() over (partition by id order by subid) = 1
             then 1 else 0
        end) as ident
from t;

Given your sample data, this simpler version would work:

select t.*, (case when subid = 1 then 1 else 0 end) as ident
from t;

If this is true for all your data, then the extra column may not even be necessary. The user can use sumif() or countif() to do the conditional logic in Excel.

Upvotes: 1

Related Questions