Apply where criteria to rows with identical values

General

Apply where criteria to rows with identical values

Hi,  My first post and relatively new to teradata SQL so would appreciate any assistance with this seemingly basic requirement.  I have to extract data from a poorly designed table where there are multiple rows created every time an action is performed on the db.  I need to apply a where condition across all rows with identical account numbers.  E.g. need to return Account Number 0002 in my results.

Account Number    Status

0001                       Actioned

0001                       Actioned

0001                       No Action

0002                       Actioned

0002                       Actioned

I have tried unsuccessfully using group by and partition by.  Please could someone guide me on the best way forward?

 

Thanks.

3 REPLIES
Teradata Employee

Re: Apply where criteria to rows with identical values

can you explain a little bit more?

are you saying that for each account number you need to retreive a single "status" value?

 

or for each account number, do you need a count of how how many rows are "Actioned" and how many are "No Action" like this:

Account Number     Acctioned      No Action

0001                               2                 1

0002                               2                 0

Re: Apply where criteria to rows with identical values

Hi, sorry I wasnt totally clear.  I just need to return any account number where all rows are Actioned.

Tags (1)
N/A

Re: Apply where criteria to rows with identical values

 

select Account_Number
from tab
group by Account_Number
-- only 'Actioned', no other value/NULL
having min(case when Status = 'Actioned' then 0 else 1 end) = 0