Search code examples
sqlsql-serversccm-2007

SQL - Select distinct based on specific columns

My SQL is a little rusty, so bear with me.

I have a query that unions two select distinct statements that pull from different sources. The data from the two select statements overlap, but each pull a date column that are not always populated, depending on the source.

I'm trying to find a way to remove duplicate records between the two statements, ignoring the date columns.

As in, if two or more records have the same PCName, Vendor, Product, and Version, one of the records is filtered, regardless of the date columns.

SELECT DISTINCT 
   SYS.Netbios_Name0 as PCName,
   ARP.Publisher0 as Vendor,
   ARP.DisplayName0 as Product,
   ARP.Version0 as Version,
   replace(replace(convert(varchar,ARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate,
   replace(replace(convert(varchar,ARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate2
FROM v_Add_Remove_Programs ARP
JOIN v_R_System SYS 
  ON ARP.ResourceID=SYS.ResourceID
WHERE SYS.Netbios_Name0 like 'CH-%' and InstallDate0 NOT LIKE ''

UNION

Select DISTINCT 
    SYS.Netbios_Name0 as PCName,
    SP.CompanyName as Vendor, 
    SP.ProductName as Product, 
    SP.ProductVersion as Version,
    replace(replace(convert(varchar,MARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate,
    replace(replace(convert(varchar,GSI.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate2
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS 
  ON SP.ResourceID=SYS.ResourceID

LEFT JOIN v_GS_Mapped_Add_Remove_Programs MARP
  ON SP.ResourceID = MARP.ResourceID
          AND RTRIM(LTRIM(UPPER(SP.ProductName))) 
                LIKE RTRIM(LTRIM(UPPER(MARP.DisplayName0)))
          AND RTRIM(LTRIM(UPPER(SP.ProductVersion))) 
                LIKE RTRIM(LTRIM(UPPER(MARP.Version0)))
LEFT JOIN v_GS_INSTALLED_SOFTWARE GSI
  ON SP.ResourceID = GSI.ResourceID
          AND RTRIM(LTRIM(UPPER(SP.ProductName))) 
                LIKE RTRIM(LTRIM(UPPER(GSI.ProductName0)))
          AND RTRIM(LTRIM(UPPER(SP.ProductVersion))) 
                LIKE RTRIM(LTRIM(UPPER(GSI.ProductVersion0)))

Where SYS.Netbios_Name0 Like 'CH-%' 
AND (MARP.InstallDate0 NOT LIKE ''
OR GSI.InstallDate0 NOT LIKE '')

ORDER By PCName, Vendor, Product, Version

Example duplicate

Solution

  • Wrap up the whole query and then partition by your primary key and filter only rownum=1

    select * from (
select *, row_number() over(partition by PCName, Vendor, Product, Version order by PCName, Vendor, Product, Version) rownum from (
SELECT   
   SYS.Netbios_Name0 as PCName,
   ARP.Publisher0 as Vendor,
   ARP.DisplayName0 as Product,
   ARP.Version0 as Version,
   replace(replace(convert(varchar,ARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate,
   replace(replace(convert(varchar,ARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate2
FROM v_Add_Remove_Programs ARP
JOIN v_R_System SYS 
  ON ARP.ResourceID=SYS.ResourceID
WHERE SYS.Netbios_Name0 like 'CH-%' and InstallDate0 NOT LIKE ''

UNION

Select  
    SYS.Netbios_Name0 as PCName,
    SP.CompanyName as Vendor, 
    SP.ProductName as Product, 
    SP.ProductVersion as Version,
    replace(replace(convert(varchar,MARP.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate,
    replace(replace(convert(varchar,GSI.InstallDate0,120),'-',''),' 00:00:00','') as InstallDate2
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS 
  ON SP.ResourceID=SYS.ResourceID

LEFT JOIN v_GS_Mapped_Add_Remove_Programs MARP
  ON SP.ResourceID = MARP.ResourceID
          AND RTRIM(LTRIM(UPPER(SP.ProductName))) 
                LIKE RTRIM(LTRIM(UPPER(MARP.DisplayName0)))
          AND RTRIM(LTRIM(UPPER(SP.ProductVersion))) 
                LIKE RTRIM(LTRIM(UPPER(MARP.Version0)))
LEFT JOIN v_GS_INSTALLED_SOFTWARE GSI
  ON SP.ResourceID = GSI.ResourceID
          AND RTRIM(LTRIM(UPPER(SP.ProductName))) 
                LIKE RTRIM(LTRIM(UPPER(GSI.ProductName0)))
          AND RTRIM(LTRIM(UPPER(SP.ProductVersion))) 
                LIKE RTRIM(LTRIM(UPPER(GSI.ProductVersion0)))

Where SYS.Netbios_Name0 Like 'CH-%' 
AND (MARP.InstallDate0 NOT LIKE ''
OR GSI.InstallDate0 NOT LIKE '')
)a)b
where rownum=1