Eliminate Duplicate record of SQL Server 2008 query

Question

I read many similar title related to my issue but I cannot apply for my case. This is my query:

select 
    d._LINE_NO, a._PROCESS_INST_NO, a._ISSUER, a._ISSUE_DATE,
    d._PROCESS_CONTENTS, d._OPT_DIVISION, 
    b._GOODS_CD, b._GOODS_NAME,b._QTY,
    a._Order_No, c._GOODS_CD, c._GOODS_NAME,a._NOTE,
    d._LINE_NO + 1 as Proc_Step_No,
    count(*) over () as Total_Rows
from  
    [ENVNDIVDB].[dbo].[TBL_PROC_PH] a
inner join 
    [ENVNDIVDB].[dbo].[TBL_PROC_PM] b on b._PROCESS_INST_NO = a._PROCESS_INST_NO
inner join 
    [ENVNDIVDB].[dbo].[TBL_PROC_PMS] c on c._PROCESS_INST_NO = a._PROCESS_INST_NO
inner join 
    [ENVNDIVDB].[dbo].[TBL_PROC_PN] d on d._PROCESS_INST_NO = a._PROCESS_INST_NO
where 
    a._PROCESS_INST_NO = '610416'

And here is the result:

It repeated 04 times. (see _PROCESS_CONTENT) And here is the original data of the Process Inst No: 610416

My question is how to eliminate duplicated records

Thanks a lot in advance.

Answer 1

I found myself solution :

;WITH cte AS
    (
        select 
            d._LINE_NO, a._PROCESS_INST_NO, a._ISSUER, a._ISSUE_DATE, d._PROCESS_CONTENTS,
            d._OPT_DIVISION,
     RANK() OVER(PARTITION BY b._GOODS_CD ORDER BY b._LINE_NO) AS Rnk,
             b._GOODS_CD , b._GOODS_NAME ,
              b._QTY,
            a._Order_No,
             c._GOODS_CD as RM_CD, c._GOODS_NAME as RM_NAME,
              a._NOTE,d._LINE_NO + 1 as Proc_Step_No,
        COUNT(*) OVER () as Total_Rows
        from
            [ENVNDIVDB].[dbo].[TBL_PROC_PH] a
        inner join [ENVNDIVDB].[dbo].[TBL_PROC_PM] b on b._PROCESS_INST_NO = a._PROCESS_INST_NO
        inner join
            [ENVNDIVDB].[dbo].[TBL_PROC_PMS] c on c._PROCESS_INST_NO = a._PROCESS_INST_NO
        inner join
            [ENVNDIVDB].[dbo].[TBL_PROC_PN] d on d._PROCESS_INST_NO = a._PROCESS_INST_NO
        where
            a._PROCESS_INST_NO = '609390' )
SELECT distinct 
        _LINE_NO, _PROCESS_INST_NO, _ISSUER, _ISSUE_DATE, _PROCESS_CONTENTS,
            _OPT_DIVISION,
             _GOODS_CD, _GOODS_NAME, _QTY,
            _Order_No, RM_CD, RM_NAME,_NOTE,
            Proc_Step_No
FROM
    cte
WHERE rnk = 1
ORDER by 
    _LINE_NO