Ruhiges IT-Forum

Code: Alle auswählen

WITH HostsRanked AS (
    SELECT
        h.uuid,
        h.computer_name,
        h.last_seen_on,
        ROW_NUMBER() OVER (PARTITION BY LOWER(h.computer_name) ORDER BY h.last_seen_on DESC) AS rank
    FROM
        hosts h
    JOIN (
        SELECT
            LOWER(computer_name) AS computer_name_normalized
        FROM
            hosts
        GROUP BY
            LOWER(computer_name)
        HAVING COUNT(LOWER(computer_name)) > 1
    ) dup ON LOWER(h.computer_name) = dup.computer_name_normalized
)
SELECT
    uuid,
    computer_name,
    last_seen_on
FROM
    HostsRanked
WHERE
    rank > 1
ORDER BY
    last_seen_on ASC;