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

MSSQL DBAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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 DBAAuthor Commented:
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 EghtebasDatabase and Application DeveloperCommented:
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
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

Learn More
PortletPaulEE Topic AdvisorCommented:
Your existing where clause will lead to unexpected results I believe. When you use OR you simply must be careful how you use it

current:

      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 = ''

suggested:

      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 = '' )

& so, with the previous advice included:
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 (
      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 = '' )
        )
    )
SELECT
      *
FROM AllPORows
WHERE ROWNUMBER BETWEEN 1 AND 100
ORDER BY ROWNUMBER

Open in new window

nb: row_number() starts at 1
Vitor MontalvãoMSSQL Senior EngineerCommented:
MSSQL DBA, do you still need help with this question?
MSSQL DBAAuthor Commented:
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 DBAAuthor Commented:
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

Vitor MontalvãoMSSQL Senior EngineerCommented:
For a SQL Server perspective those rows aren't duplicated since they have differen FOC_DATE values.
MSSQL DBAAuthor Commented:
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.
PortletPaulEE Topic AdvisorCommented:
Supression of values should be done in the reporting tool, not SQL.

Are you using SSRS?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
PortletPaulEE Topic AdvisorCommented:
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.
Brendt HessSenior DBACommented:
If I am interpreting your request correctly, you need to return the first row in your return set that contains a given PO Number. I would do something like the following, based off this query above:
http://www.experts-exchange.com/questions/28726040/MS-SQL-Server-Query.html#a41017707  
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 =''))
---- my new code block ----
SELECT TOP 100
    AllPORows.*
FROM AllPORows 
INNER JOIN (
   SELECT PURCH_ORDER, min(ROWNUMBER) as RNum
   FROM AllPORows 
   GROUP BY PURCH_ORDER
   ) Filter
   ON AllPORows.PURCH_ORDER = Filter.PURCH_ORDER
   AND AllPORows.ROWNUMBER = Filter.Rnum
ORDER BY AllPORows.ROWNUMBER

Open in new window

The subquery named Filter will return the minimum row number attached to any given PO. When you JOIN that back to the AllPORows query, it will return the rows you are looking for.  However, the ROWNUMBER will not be sequential. Instead, it will return a list of row numbers that has many gaps, something like:
  1,3,4,7,8,10,13,15,16,17,18,19,23, ...
If you need the numbers to be sequential, SELECT the fields you need to display by name, then change the SELECT statement to include another ROW_NUMBER(), so the code is like this:
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 =''))
---- my new code block ----
SELECT TOP 100
    ROW_NUMBER() OVER (ORDER BY ROWNUMBER) 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 AllPORows 
INNER JOIN (
   SELECT PURCH_ORDER, min(ROWNUMBER) as RNum
   FROM AllPORows 
   GROUP BY PURCH_ORDER
   ) Filter
   ON AllPORows.PURCH_ORDER = Filter.PURCH_ORDER
   AND AllPORows.ROWNUMBER = Filter.Rnum
ORDER BY AllPORows.ROWNUMBER

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
>>"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)."

If you are only after the "first row" then there has to be some logic for choosing what is first, second etc. Below I have used this

            ROW_NUMBER() OVER (PARTITION BY PURCH_ORDER
                               ORDER BY FOC_DATE DESC) AS NOREPEATS

partition by allows one row_number() sequence for each purch_order value, and
ORDER BY FOC_DATE DESC determines which row get the value of 1
So, you can alter the order by to suit your needs.

SELECT  *
FROM (
      SELECT
            ROW_NUMBER() OVER (PARTITION BY PURCH_ORDER 
                               ORDER BY FOC_DATE DESC) AS NOREPEATS
          , 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 = '' )
      ) AS D
WHERE NOREPEATS = 1

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.