Thanks a million for differing me to the old thread. I got the answer from there .

WITH Matt_RemoveDuplicateServers AS

(

select

ROW_NUMBER() OVER(PARTITION BY ser.servername ORDER BY ser.servername DESC) AS RowID,

Usage.UsageTypeName,

IT.InstanceTypeName,

I.InstanceName,

ser.servername,

cust.customername,

dc.datacentername,

st.servertypename

from [dbo].[InstanceTypes] IT

join [dbo].[Instances] I on IT.InstanceTypeID = I.InstanceTypeID

join [dbo].[InstanceServers] ITS on ITS.InstanceID = I.InstanceID

join Servers Ser on Ser.ServerID = ITS.ServerID

Join [dbo].[UsageTypes] Usage on Ser.UsagetypeID = Usage.UsageTypeID

join [dbo].[DataCenters] DC on DC.DataCenterID = Ser.DataCenterID

join [dbo].[ServerTypes] ST on ST.ServerTypeId = Ser.ServerTypeId

join [dbo].[Customers] cust on cust.customerid = I.customerid

where I.Active = 1 and usagetypename != 'Decommissioned' and InstanceTypeName = 'Perforce'

)

select * from Matt_RemoveDuplicateServers where RowID = 1