MS SQL Server Query

Hi All,

Below query resulting duplicate records with same [PURCH_ORDER]. We need rows having unique [PURCH_ORDER] with all the condition given.

Please advise how to display unique [PURCH_ORDER] with all the condition given.

Thanks in advance.

with AllPORows as 
(select ROW_NUMBER() OVER (order by PURCH_ORDER DESC) as ROWNUMBER, CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, 
L_DDD, FOC_DATE, LFOC_Sent, COMPLETION_DATE, LOC_LATA, LOC_REGION, LOC_STATE, LOC_CITY, DATE_MM, VW_FOC__CKT_DEL, VW_DESR_DUE__FOC, VW_INST_COMPLT__WANT, VW_LEC_CDEL__INST_COMPLT 
from TBL_PIE_CUST_TDM_WLN_NB_DS0 
where CR_NAME in ('AT&T','CenturyLink') 
AND DATE_MM in ('8') 
AND DATE_YYYY in (2015)  
AND LOC_REGION IS NOT NULL 
AND LOC_LATA IS NOT NULL 
AND ACT_TYPE IS NOT NULL 
AND FOC_DATE IS NOT NULL OR FOC_DATE ='' 
AND COMPLETION_DATE IS NOT NULL OR COMPLETION_DATE ='' 
AND PURCH_ORDER IS NOT NULL OR PURCH_ORDER =''  
union all 
select ROW_NUMBER() OVER (order by PURCH_ORDER DESC) as ROWNUMBER, CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, 
L_DDD, FOC_DATE,LFOC_Sent, COMPLETION_DATE, LOC_LATA, LOC_REGION, LOC_STATE, LOC_CITY, DATE_MM, VW_FOC__CKT_DEL, VW_DESR_DUE__FOC,VW_INST_COMPLT__WANT, VW_LEC_CDEL__INST_COMPLT 
from TBL_PIE_CUST_TDM_WLN_NB_DS1 
where CR_NAME in ('AT&T','CenturyLink') 
AND DATE_MM in ('8') 
AND DATE_YYYY in (2015)  
AND LOC_REGION IS NOT NULL 
AND LOC_LATA IS NOT NULL 
AND ACT_TYPE IS NOT NULL 
AND FOC_DATE IS NOT NULL OR FOC_DATE ='' 
AND COMPLETION_DATE IS NOT NULL OR COMPLETION_DATE ='' 
AND PURCH_ORDER IS NOT NULL OR PURCH_ORDER ='' ) 
select * from AllPORows where ROWNUMBER between 0 and 100 

Select all Open in new window