remove multiple duplicate columns sql-CodePudding

UUID    DEVICE_ID   DT
900 600        7
900 900        5
900 600        5
900 900        7
900 400        7
900 800        5
901 800        7
901 900        7
901 400        5
901 400        7
901 800        5

the answer should be max of dt and no duplicates of uuid and device_id EX:

900 900        7
901 800        7

CodePudding user response：

Use ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY UUID ORDER BY DT DESC) rn
    FROM yourTable
)

SELECT UUID, DEVICE_ID, DT
FROM cte
WHERE rn = 1;

CodePudding user response：

You can use a couple of Common Table Expressions (CTE) as placeholders to arrive at the correct result.

For example, the first CTE below selects All Devices including the maximum DT for each UUID. The second CTE selects devices corresponding to max DT. The third CTE provides the logic which removes all duplicate UUID and DEVICE_IDs, ensuring that once a DEVICE_ID has been selected, it is then ignored for subsequent UUIDs.

WITH AllDevice AS (
SELECT *,
MAX(DT) OVER (PARTITION BY UUID ORDER BY UUID) AS max_dt
FROM yourTable
),
MaxDT AS (
SELECT * 
FROM AllDevice
WHERE DT = max_dt
),
NoDuplicates AS (
SELECT MIN(UUID) OVER (PARTITION BY DEVICE_ID ORDER BY DEVICE_ID) AS UUID,
       DEVICE_ID, max_dt AS DT
FROM MaxDT)
SELECT UUID, MAX(DEVICE_ID) AS DEVICE_ID, MAX(DT) AS DT
FROM NoDuplicates
GROUP BY UUID;

See Demo

Results

UUID | DEVICE_ID | DT
---: | --------: | -:
 900 |       900 |  7
 901 |       800 |  7