CODEWITHSUNDEEP
sqlsql-server
coolstoner
coolstoner

Reputation: 799

Remove duplicate entry of column from SQL Server query

I would like to remove all duplicates from the query to return distinct rows of records...

SELECT 
    DISTINCT (rme.RoleMappingEmployeeKey) AS role_key_one,
    me.EmpSAPID AS emp_sap_id,
    me.SrKey AS emp_sr_key,
    CONCAT(me.EmpFirstName,' ',me.EmpLastName) AS emp_name,
    rm.RoleName AS emp_role_name,
    rm.SubmittedDate AS emp_role_given_date,
    ms.SiteName AS site_name,
    hd.HRDeptName AS dept_name,
    hsd.HRSDName AS sub_dept_name,
    mc2.ClientName AS client_name,
    mb.BusinessTypeName AS bus_type,
    mc3.CompName AS comp_name,
    hem.SeparationDate AS lwd
FROM 
    dbo.RoleMappingEmployee rme
LEFT JOIN 
    dbo.MasterEmp me ON me.SrKey = rme.SrKey
LEFT JOIN 
    dbo.MasterCountry mc ON (mc.CountryKey = rme.CountryKey OR mc.CountryKey = me.CountryKey)
LEFT JOIN 
    dbo.RoleMaster rm ON rm.RoleKey = rme.RoleKey
LEFT JOIN 
    dbo.HRMasterEmployeeMain hem ON (hem.SrKey = me.SrKey AND hem.EmployeeStatus=10)
LEFT JOIN 
    dbo.HRMapping_SubDept_SBand_Desig_SubFunction hsdsdsf ON hsdsdsf.HRSubDeptBDSbKey = hem.HRSubDeptBDSbKey
LEFT JOIN 
    dbo.HRMasterSubDepartment hsd ON (hsd.HRSDKey = rme.SubDeptKey OR hsd.HRSDKey = hsdsdsf.HRSDKey)
LEFT JOIN 
    dbo.HRMasterDepartment hd ON ( hd.HRDeptKey = rme.DeptKey OR hd.HRDeptKey = hsd.HRDeptKey)
LEFT JOIN 
    dbo.MasterSite ms ON (ms.SiteKey = rme.SiteKey OR ms.SiteKey = me.SiteKey)
LEFT JOIN 
    dbo.MasterClient mc2 ON (mc2.ClientKey = rme.ClientKey OR mc2.ClientKey = hd.ClientKey)
LEFT JOIN 
    dbo.MasterBusiness mb ON (mb.BusinessTypeKey = rme.BusinessTypeKey OR mb.BusinessTypeKey = hd.BusinessTypeKey)
LEFT JOIN 
    dbo.MasterComp mc3 ON (mc3.CompKey = rme.CompKey OR mc3.CountryKey = mc.CountryKey)
WHERE
    me.IsActive = 0
ORDER BY
    rme.RoleMappingEmployeeKey DESC

This query returns a result as shown in this screenshot:

Check the value highlighted below

the RoleMappingEmployeeKey AS role_key_one, is repeated though i have mentioned the keyword DISTINCT for the column in the query...

Upvotes: 0

Views: 99

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7979

DISCLAIMER

if DISTINCT "doesn't work" it means that there IS a difference between "repeated" columns. So, with "duplicates removal" you will lose something - the difference I mentioned above. But... if you do not care about this difference, you could use something like this:

WITH dedup as
(
    SELECT 
        (rme.RoleMappingEmployeeKey) AS role_key_one,
        row_number() over (partition by rme.RoleMappingEmployeeKey order by rm.SubmittedDate) [role_key_one_rank],
        me.EmpSAPID AS emp_sap_id,
        me.SrKey AS emp_sr_key,
        CONCAT(me.EmpFirstName,' ',me.EmpLastName) AS emp_name,
        rm.RoleName AS emp_role_name,
        rm.SubmittedDate AS emp_role_given_date,
        ms.SiteName AS site_name,
        hd.HRDeptName AS dept_name,
        hsd.HRSDName AS sub_dept_name,
        mc2.ClientName AS client_name,
        mb.BusinessTypeName AS bus_type,
        mc3.CompName AS comp_name,
        hem.SeparationDate AS lwd
    FROM 
        dbo.RoleMappingEmployee rme
    LEFT JOIN 
        dbo.MasterEmp me ON me.SrKey = rme.SrKey
    LEFT JOIN 
        dbo.MasterCountry mc ON (mc.CountryKey = rme.CountryKey OR mc.CountryKey = me.CountryKey)
    LEFT JOIN 
        dbo.RoleMaster rm ON rm.RoleKey = rme.RoleKey
    LEFT JOIN 
        dbo.HRMasterEmployeeMain hem ON (hem.SrKey = me.SrKey AND hem.EmployeeStatus=10)
    LEFT JOIN 
        dbo.HRMapping_SubDept_SBand_Desig_SubFunction hsdsdsf ON hsdsdsf.HRSubDeptBDSbKey = hem.HRSubDeptBDSbKey
    LEFT JOIN 
        dbo.HRMasterSubDepartment hsd ON (hsd.HRSDKey = rme.SubDeptKey OR hsd.HRSDKey = hsdsdsf.HRSDKey)
    LEFT JOIN 
        dbo.HRMasterDepartment hd ON ( hd.HRDeptKey = rme.DeptKey OR hd.HRDeptKey = hsd.HRDeptKey)
    LEFT JOIN 
        dbo.MasterSite ms ON (ms.SiteKey = rme.SiteKey OR ms.SiteKey = me.SiteKey)
    LEFT JOIN 
        dbo.MasterClient mc2 ON (mc2.ClientKey = rme.ClientKey OR mc2.ClientKey = hd.ClientKey)
    LEFT JOIN 
        dbo.MasterBusiness mb ON (mb.BusinessTypeKey = rme.BusinessTypeKey OR mb.BusinessTypeKey = hd.BusinessTypeKey)
    LEFT JOIN 
        dbo.MasterComp mc3 ON (mc3.CompKey = rme.CompKey OR mc3.CountryKey = mc.CountryKey)
    WHERE
        me.IsActive = 0
)
select distinct *
from dedup
where [role_key_one_rank] = 1
ORDER BY role_key_one DESC

Upvotes: 1

Related Questions