SELECT
nodeaccesspoint,
portname,
ipaddress,
instances
FROM [dbo].[UDT_MACCurrentInformation]
INNER JOIN (
SELECT
ipaddress as ip,
count(ipaddress) as instances
FROM [dbo].[UDT_MACCurrentInformation]
GROUP BY ipaddress
HAVING COUNT(*) > 1
) dt ON ipaddress = dt.ip
ORDER BY instances desc, ipaddress desc, nodeaccesspoint desc, portname asc
SELECT
nodeaccesspoint,
portname,
macaddress,
instances
FROM [dbo].[UDT_MACCurrentInformation]
INNER JOIN (
SELECT
macaddress as mac,
count(macaddress) as instances
FROM [dbo].[UDT_MACCurrentInformation]
GROUP BY macaddress
HAVING COUNT(*) > 1
) dt ON macaddress = dt.mac
ORDER BY instances desc, macaddress desc, nodeaccesspoint desc, portname asc
