Remove Almost Duplicate Rows in SQL from a select query

Question

I'm trying to select some data but I'm getting 'almost duplicated rows' - all of the columns are the same except the 'gtin' column, the result I'm expecting is to get only one line of each of those 'almost duplicated rows'

The result I'm trying to achieve is to only get row 1 & 3.

this is the query I'm using:

SELECT DISTINCT 'Y' AS isEnterprise,
                   ii.ITEM_ID AS itemId,
                   ii.PARENT_ITEM_ID as PID,
                   ii.ITEM_IDENTIFIER AS productNo,
                   id.DESCRIPTION_1 AS description,
                   id.DESCRIPTION_4 AS packValue,
                   pid.PACKAGE_IDENTIFIER AS gtin,
                   pac.PRE_PRICE_AMOUNT AS eachPrice
   FROM PWRNXGDTA.ITEM_INFORMATION AS ii
   JOIN PWRNXGDTA.ITEM_ASSORTMENT AS ia ON ii.ITEM_ID = ia.ITEM_ID
   JOIN PWRNXGDTA.ITEM_DESCRIPTION AS id ON id.ITEM_ID = ii.ITEM_ID
   JOIN PWRNXGDTA.BUSINESS_UNIT AS bu ON ii.LOGISTICS_BU_ID = bu.BU_ID
   LEFT JOIN PWRNXGDTA.ITEM_EXTN_NONMDM AS ien ON ien.ITEM_IDENTIFIER = ii.ITEM_IDENTIFIER
   LEFT JOIN PWRNXGDTA.LKP_CLASSIFICATION_CLASS AS lcc ON lcc.CLASSIFICATION_CLASS_ID = ii.CLASSIFICATION_CLASS_ID
   LEFT JOIN PWRNXGDTA.LKP_CLASSIFICATION_PBH AS lcp ON lcp.CLASSIFICATION_PBH_ID = ii.CLASSIFICATION_PBH_ID
   LEFT JOIN PWRNXGDTA.LKP_CLASSIFICATION_PBHF AS lcpf ON lcpf.CLASSIFICATION_PBHF_ID = ii.CLASSIFICATION_PBHF_ID
   LEFT JOIN PWRNXGDTA.LIST_PROPRIETARY_ITEM AS lpi ON lpi.ITEM_IDENTIFIER = ii.ITEM_IDENTIFIER
   JOIN PWRNXGDTA.PA_ASSORTMENT AS pa ON pa.PACKAGE_ASSORTMENT_ID = ia.PACKAGE_ASSORTMENT_ID
   JOIN PWRNXGDTA.ITEM_PRODUCT AS ip ON ip.ITEM_ID = ii.ITEM_ID
   JOIN PWRNXGDTA.PRODUCT_INFO AS pin ON pin.PRODUCT_ID = ip.PRODUCT_ID
   LEFT JOIN PWRNXGDTA.PRODUCT_NUTRITIONAL AS pn ON pn.PRODUCT_ID = ip.PRODUCT_ID
   LEFT JOIN PWRNXGDTA.LKP_BRAND_INFORMATION AS lbi ON lbi.BRAND_ID = pin.BRAND_ID
   AND lbi.RECORD_STATUS = 'A'
   JOIN PWRNXGDTA.PACKAGE_IDENTIFIER AS pid ON pid.PACKAGE_ID = pa.PACKAGE_ID
   JOIN PWRNXGDTA.PA_CONSUMABLE AS pac ON pac.PACKAGE_ASSORTMENT_ID = pa.PACKAGE_ASSORTMENT_ID 
   where id.DESCRIPTION_1 like '%HAAGEN DAZ VAN MLK CHO BR%'

Please help, thanks.

Answer 1

Another way is to use Row_Number to avoid this group by:

Select * from (
    SELECT DISTINCT 'Y' AS isEnterprise,
                    ii.ITEM_ID AS itemId,
                    ii.PARENT_ITEM_ID as PID,
                    ii.ITEM_IDENTIFIER AS productNo,
                    id.DESCRIPTION_1 AS description,
                    id.DESCRIPTION_4 AS packValue,
                    pid.PACKAGE_IDENTIFIER AS gtin,
                    RowN = Row_Number() over (Partition by ii.Item_Id, ii.Parent_Item_id order by pid.PACKAGE_IDENTIFIER) --Added new row_number if you want max you can use desc
                    pac.PRE_PRICE_AMOUNT AS eachPrice
    FROM PWRNXGDTA.ITEM_INFORMATION AS ii
    JOIN PWRNXGDTA.ITEM_ASSORTMENT AS ia ON ii.ITEM_ID = ia.ITEM_ID
    JOIN PWRNXGDTA.ITEM_DESCRIPTION AS id ON id.ITEM_ID = ii.ITEM_ID
    JOIN PWRNXGDTA.BUSINESS_UNIT AS bu ON ii.LOGISTICS_BU_ID = bu.BU_ID
    LEFT JOIN PWRNXGDTA.ITEM_EXTN_NONMDM AS ien ON ien.ITEM_IDENTIFIER = ii.ITEM_IDENTIFIER
    LEFT JOIN PWRNXGDTA.LKP_CLASSIFICATION_CLASS AS lcc ON lcc.CLASSIFICATION_CLASS_ID = ii.CLASSIFICATION_CLASS_ID
    LEFT JOIN PWRNXGDTA.LKP_CLASSIFICATION_PBH AS lcp ON lcp.CLASSIFICATION_PBH_ID = ii.CLASSIFICATION_PBH_ID
    LEFT JOIN PWRNXGDTA.LKP_CLASSIFICATION_PBHF AS lcpf ON lcpf.CLASSIFICATION_PBHF_ID = ii.CLASSIFICATION_PBHF_ID
    LEFT JOIN PWRNXGDTA.LIST_PROPRIETARY_ITEM AS lpi ON lpi.ITEM_IDENTIFIER = ii.ITEM_IDENTIFIER
    JOIN PWRNXGDTA.PA_ASSORTMENT AS pa ON pa.PACKAGE_ASSORTMENT_ID = ia.PACKAGE_ASSORTMENT_ID
    JOIN PWRNXGDTA.ITEM_PRODUCT AS ip ON ip.ITEM_ID = ii.ITEM_ID
    JOIN PWRNXGDTA.PRODUCT_INFO AS pin ON pin.PRODUCT_ID = ip.PRODUCT_ID
    LEFT JOIN PWRNXGDTA.PRODUCT_NUTRITIONAL AS pn ON pn.PRODUCT_ID = ip.PRODUCT_ID
    LEFT JOIN PWRNXGDTA.LKP_BRAND_INFORMATION AS lbi ON lbi.BRAND_ID = pin.BRAND_ID
    AND lbi.RECORD_STATUS = 'A'
    JOIN PWRNXGDTA.PACKAGE_IDENTIFIER AS pid ON pid.PACKAGE_ID = pa.PACKAGE_ID
    JOIN PWRNXGDTA.PA_CONSUMABLE AS pac ON pac.PACKAGE_ASSORTMENT_ID = pa.PACKAGE_ASSORTMENT_ID 
    where id.DESCRIPTION_1 like '%HAAGEN DAZ VAN MLK CHO BR%'
) a
Where a.RowN = 1

Answer 2

You can't do it with distinct, since distinct works on full rows only. Your best option is to use group by:

SELECT 'Y' AS isEnterprise,
       ii.ITEM_ID AS itemId,
       ii.PARENT_ITEM_ID as PID,
       ii.ITEM_IDENTIFIER AS productNo,
       id.DESCRIPTION_1 AS description,
       id.DESCRIPTION_4 AS packValue,
       MIN(pid.PACKAGE_IDENTIFIER) AS gtin, -- Or max, if you want to...
       pac.PRE_PRICE_AMOUNT AS eachPrice
   FROM PWRNXGDTA.ITEM_INFORMATION AS ii
   JOIN PWRNXGDTA.ITEM_ASSORTMENT AS ia ON ii.ITEM_ID = ia.ITEM_ID
   JOIN PWRNXGDTA.ITEM_DESCRIPTION AS id ON id.ITEM_ID = ii.ITEM_ID
   JOIN PWRNXGDTA.BUSINESS_UNIT AS bu ON ii.LOGISTICS_BU_ID = bu.BU_ID
   LEFT JOIN PWRNXGDTA.ITEM_EXTN_NONMDM AS ien ON ien.ITEM_IDENTIFIER = ii.ITEM_IDENTIFIER
   LEFT JOIN PWRNXGDTA.LKP_CLASSIFICATION_CLASS AS lcc ON lcc.CLASSIFICATION_CLASS_ID = ii.CLASSIFICATION_CLASS_ID
   LEFT JOIN PWRNXGDTA.LKP_CLASSIFICATION_PBH AS lcp ON lcp.CLASSIFICATION_PBH_ID = ii.CLASSIFICATION_PBH_ID
   LEFT JOIN PWRNXGDTA.LKP_CLASSIFICATION_PBHF AS lcpf ON lcpf.CLASSIFICATION_PBHF_ID = ii.CLASSIFICATION_PBHF_ID
   LEFT JOIN PWRNXGDTA.LIST_PROPRIETARY_ITEM AS lpi ON lpi.ITEM_IDENTIFIER = ii.ITEM_IDENTIFIER
   JOIN PWRNXGDTA.PA_ASSORTMENT AS pa ON pa.PACKAGE_ASSORTMENT_ID = ia.PACKAGE_ASSORTMENT_ID
   JOIN PWRNXGDTA.ITEM_PRODUCT AS ip ON ip.ITEM_ID = ii.ITEM_ID
   JOIN PWRNXGDTA.PRODUCT_INFO AS pin ON pin.PRODUCT_ID = ip.PRODUCT_ID
   LEFT JOIN PWRNXGDTA.PRODUCT_NUTRITIONAL AS pn ON pn.PRODUCT_ID = ip.PRODUCT_ID
   LEFT JOIN PWRNXGDTA.LKP_BRAND_INFORMATION AS lbi ON lbi.BRAND_ID = pin.BRAND_ID
   AND lbi.RECORD_STATUS = 'A'
   JOIN PWRNXGDTA.PACKAGE_IDENTIFIER AS pid ON pid.PACKAGE_ID = pa.PACKAGE_ID
   JOIN PWRNXGDTA.PA_CONSUMABLE AS pac ON pac.PACKAGE_ASSORTMENT_ID = pa.PACKAGE_ASSORTMENT_ID 
   where id.DESCRIPTION_1 like '%HAAGEN DAZ VAN MLK CHO BR%'
GROUP BY ii.ITEM_ID, 
         ii.PARENT_ITEM_ID, 
         ii.ITEM_IDENTIFIER, 
         id.DESCRIPTION_1,
         id.DESCRIPTION_4,
         pac.PRE_PRICE_AMOUNT