MS SQL Server Query

union all allows duplicate. Change it to union. But, be aware that union is slower than union all.

Also, have ROW_NUMBER() outside your first cte.

;with cte1
(Select
.
.
Union
.
.
),
cte2
( Select Row_Number... From cte1)
Select * From cte2
where ROWNUMBER between 0 and 100

;with AllPORows as 
(select 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 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 ='' ), 
AllPORows2 AS(
select ROW_NUMBER() OVER (order by PURCH_ORDER DESC) as ROWNUMBER, * from AllPORows 
)
Select  * from AllPORows2 where ROWNUMBER between 0 and 100 

Select allOpen in new window

Thanks Mike.

I have modified below query as per your suggestion, Can you pls review below query once again.

;with AllPORows as 
(select 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  
select 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 ='' ), 
AllPORows2 AS(
select ROW_NUMBER() OVER (order by PURCH_ORDER DESC) as ROWNUMBER, * from AllPORows 
)
Select  * from AllPORows2 where ROWNUMBER between 0 and 100 

Select allOpen in new window

It look good to me. All I can do now is cross my fingers and wait for you to test it.

You may need to add

Order By ROWNUMBER

at the very end

MSSQL DBA, do you still need help with this question?

Hi Mike,Paul & Vitor,

Yes, I need your more help on below query. The below query (without union) is still displaying duplicate rows. Please suggest how to modify below query to eliminate duplicate purch_order.

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_ETHERNET_WLN
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 allOpen in new window

Ex:
1. Row 3 & 4 duplicate PURCH_ORDER coming we need only row 3.
2. same for row 5 & 6...row 5 & 6 duplicate PURCH_ORDER coming we need only row 5.
I am herewith attaching sample output of above query for your reference.

Just an thought: for each child select clause, do we need to add rowid number?? In this case,row 3 would give id 1 & row 4 would give id 2. we shal only consider rowid = 1

Please advise with query.
Thanks in advance.
Book2.xlsx

Tried with below query, still no luck..its still displaying duplicate purch order

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_ETHERNET_WLN
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 order by ROWNUMBER

Select allOpen in new window

Hi Vitor,  Yes you are correct.
Row 3 & 4 are same purch order and I want to display only row 3 (first row id) in my report. Can you please give me logic/query for this scenario?

Similarly row 5 & 6 and I want to display row 5 in my report (first row id).

Appreciate your help/or suggestion.

Supression of values should be done in the reporting tool, not SQL.

Are you using SSRS?

Try this:

WITH CTE_GroupRows AS (
	SELECT CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, 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, MIN(FOC_DATE) AS MIN_FOC_DATE
	FROM TBL_PIE_CUST_ETHERNET_WLN
	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 
		AND COMPLETION_DATE IS NOT NULL 
		AND PURCH_ORDER IS NOT NULL 
	GROUP BY CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, 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
	),
AllPORows AS (
	SELECT ROW_NUMBER() OVER (ORDER BY PURCH_ORDER DESC) AS ROWNUMBER, 
		CR_NAME, PRODUCT_NAME, PURCH_ORDER, FTN, LASR_SENT, L_DDD, MIN_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 CTE_GroupRows
SELECT *
FROM AllPORows
WHERE ROWNUMBER BETWEEN 0 AND 100 order by ROWNUMBER

Select allOpen in new window

NOTE: I removed the OR's since those criteria were redundant because empty string ('') is already NOT NULL so already covered by the IS NOT NULL criteria.

The repetition of PURCH_ORDER (yellow highlight) is NOT duplication! and the rows ARE "distinct"

Notice the blue highlights, these are some of the differences in the rows

This is how SQL behaves. It WILL repeat the PURCH_ORDER in such circumstances and it is correct behaviour for SQL.

PLEASE inform us about how you are preparing the actual report. Most reporting tools have their own methods for suppressing the output of certain information. It is way more efficient to do it in the reporting tool than attempting to do it in SQL.

For example, of you are preparing the "report" as an HTML page using PHP, then "suppress" the output of PURCH_ORDER when not wanted using PHP.