Ranked Priority Query

ranked_priority_query.sql

WITH RankedData AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY CASE WHEN type = 'I' THEN 0 ELSE 1 END) AS TypePriority
    FROM your_table_name
)

SELECT id, name, type
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY CASE WHEN type = 'E' THEN 0 ELSE 1 END) AS ETypePriority
    FROM RankedData
    WHERE TypePriority = 1
) AS CombinedData
WHERE TypePriority = 1 OR (TypePriority > 1 AND ETypePriority = 1);