SQL Query to find duplicates from siamese twins machines!!

SELECT
[Name0],
[resourceid],
[Obsolete],
[Client],
[LastSWscan],
[LastHWscan]
FROM
(
select
row_number() over (partition by a.Name0 order by c.Lasthwscan desc) as rownum,
a.Name0 as [Name0],
a.resourceid as [resourceid],
a.Obsolete0 as [Obsolete],
a.Client0 as [Client],
b.Lastscandate as [LastSWscan],
c.Lasthwscan as [Lasthwscan]

from v_r_system AS a
LEFT OUTER JOIN v_GS_LastSoftwareScan AS b
ON a.resourceid = b.resourceid
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS AS c
ON a.resourceid = c.resourceid
WHERE a.Name0 IN (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) > 1)
AND (c.LastHWscan is NOT NULL and b.LastScandate is NOT NULL and a.Client0=1 and a.Obsolete0=0 and a.Active0=1)) as dt
where rownum<>1
order by [Lasthwscan] desc

Description: Obsolete machines are common in all the SMS\SCCM environments. This can be easily tracked and maintained by checking the Obsolete flag from the v_r_system view. But what if you have more than one entry with the same hostname which is active non Obsolete and client=1 and with recent hardware\software and discovery information's?...It is sometimes very tough to differentiate the real one from the duplicate. The above query is written to overcome this issue. The query lists all those boxes which have an older hardware scan time compare to it's twin machine.(and 99% of times, this gives you the correct value).

To double check, take random number of hostnames from the above query result, and provide in the where condition of below query...

select a.Netbios_name0, a.resourceid,a.Creation_date0,a.client0, a.ad_site_name0,a.Creation_date0,a.obsolete0,
b.sms_installed_sites0 as 'sitecode',c.Lasthwscan,d.Agenttime
from v_r_system a
left outer join v_RA_System_SMSInstalledSites b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
Left outer join v_AgentDiscoveries d on d.resourceid=a.resourceid
where a.Netbios_name0 like 'machinenamehere' and d.AgentName='Heartbeat Discovery'