TABLE dsm_hardware_basic
(
[UUID] binary(16) -- Randomly generated 16 digit key that is unique for each record, only column with no duplicate rows.
[HostUUID] binary(16) -- Randomly generated 16 digit key, column has duplicate rows.
[Name] nvarchar(255) -- Column that contains hostnames of computer assets. Example of record: PCASSET001. Column has duplicate rows.
[LastAgentExecution] datetime -- The last time that the software agent that collects asset information ran on the PC.
[HostName] nvarchar(255) -- The fully qualified domain name of the PC. Example of record: PCASSET001.companydomain.com. Column has duplicate rows.
)

SELECT   ,dsm_hardware_basic.[HostUUID]
             ,MIN(dsm_hardware_basic.[LastAgentExecution]) AS [LastAgentExecution]
    FROM      dsm_hardware_basic
    WHERE     dsm_hardware_basic.[HostUUID] <> ''
    GROUP BY  dsm_hardware_basic.[HostUUID]
              HAVING COUNT(*) = 2 -- The tiny amount of rows where this count is >2 will be left alone.

SELECT   ,dsm_hardware_basic.[HostName]
             ,MIN(dsm_hardware_basic.[LastAgentExecution]) AS [LastAgentExecution]
    FROM      dsm_hardware_basic
    WHERE     dsm_hardware_basic.[HostName] <> ''
    GROUP BY  dsm_hardware_basic.[HostName]
              HAVING COUNT(*) > 1

SELECT   ,dsm_hardware_basic.[Name]
             ,MIN(dsm_hardware_basic.[LastAgentExecution]) AS [LastAgentExecution]
    FROM      dsm_hardware_basic
    WHERE     dsm_hardware_basic.[Name] <> ''
    GROUP BY  dsm_hardware_basic.[Name]
              HAVING COUNT(*) = 2 -- The tiny amount of rows where this count is >2 will be left alone.

SELECT
    *
FROM dsm_hardware_basic

SELECT
    dsm_hardware_basic.*
FROM dsm_hardware_basic
INNER JOIN
    (
        SELECT [UUID], ROW_NUMBER() OVER
            (PARTITION BY [HostUUID]
             ORDER BY [LastAgentExecution] DESC) AS host_UUID_rank
        FROM dsm_hardware_basic
        WHERE
            [HostUUID] <> ''
    ) AS
    duplicate_host_UUID_filtered ON dsm_hardware_basic.UUID = duplicate_host_UUID_filtered.UUID
    AND duplicate_host_UUID_filtered.host_UUID_rank = 1

SELECT
    dsm_hardware_basic.*
FROM dsm_hardware_basic
INNER JOIN
    (
        SELECT [UUID], ROW_NUMBER() OVER
            (PARTITION BY [HostUUID]
             ORDER BY [LastAgentExecution] DESC) AS host_UUID_rank
        FROM dsm_hardware_basic
        WHERE
            [HostUUID] <> ''
    ) AS
    duplicate_host_UUID_filtered ON dsm_hardware_basic.UUID = duplicate_host_UUID_filtered.UUID
    AND duplicate_host_UUID_filtered.host_UUID_rank = 1
INNER JOIN
    (
        SELECT [UUID], ROW_NUMBER() OVER
            (PARTITION BY [HostName]
             ORDER BY [LastAgentExecution] DESC) AS host_UUID_rank
        FROM dsm_hardware_basic
        WHERE
            [HostName] <> ''
    ) AS
    duplicate_HostName_filtered ON dsm_hardware_basic.UUID = duplicate_HostName_filtered.UUID
    AND duplicate_HostName_filtered.host_UUID_rank = 1