Avatar of MSSQL DBA
MSSQL DBA
 asked on

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 

Open in new window

DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Mike Eghtebas

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 

Open in new window

MSSQL DBA

ASKER
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 

Open in new window

Mike Eghtebas

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

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

ASKER
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 

Open 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
MSSQL DBA

ASKER
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

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
MSSQL DBA

ASKER
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.
PortletPaul

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

Are you using SSRS?
Vitor Montalvão

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

Open 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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
PortletPaul

NOT duplicatesThe 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.
ASKER CERTIFIED SOLUTION
Brendt Hess

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.