--original: FROM LTE_HIST_MESH3_DAILY

SELECT * FROM (
    SELECT
        MAX(Type) AS Type,
        2 AS Part,
        'AGG' AS AggKey,
        NULL AS Cluster,
        '2021-08-04' AS Date,
        TimeZone,
        SUM(RowsAggregated) AS RowsAggregated,
        -1003 AS LevelID,
        Longitude,
        Latitude,
        ElementID,
        DL_EARFCN,
        histType,
        dimValue,
        SUM(dimCount) AS dimCount,
        RANK() OVER (PARTITION BY histType, ElementID, DL_EARFCN ORDER BY SUM(dimCount) DESC, dimValue DESC) AS rank
    FROM t1
    WHERE Date = '2021-08-04'
      AND Part = 2 
      AND Longitude = -112.508500 
      AND Latitude = 33.474500 
      AND histType IN ('CE_BestPCICount','CE_LTEServingSectorHandleCount','CE_MDTBestPCICount','CE_MDTServingPCICount','CE_MDTServingSectorHandleCount','CE_ServingPCICount')
    GROUP BY 
        TimeZone, Longitude, Latitude, ElementID, DL_EARFCN, histType, dimValue

    UNION ALL

    SELECT
        MAX(Type) AS Type,
        2 AS Part,
        'AGG' AS AggKey,
        NULL AS Cluster,
        '2021-08-04' AS Date,
        TimeZone,
        SUM(RowsAggregated) AS RowsAggregated,
        -1003 AS LevelID,
        Longitude,
        Latitude,
        ElementID,
        NULL AS DL_EARFCN, -- Matched alias name with top branch
        histType,
        dimValue,
        SUM(dimCount) AS dimCount,
        RANK() OVER (PARTITION BY histType, ElementID ORDER BY SUM(dimCount) DESC, dimValue DESC) AS rank
    FROM t1
    WHERE Date = '2021-08-04'
      AND Part = 2 
      AND Longitude = -112.508500 
      AND Latitude = 33.474500 
      AND histType IN ('CE_BestPCICount','CE_LTEServingSectorHandleCount','CE_MDTBestPCICount','CE_MDTServingPCICount','CE_MDTServingSectorHandleCount','CE_ServingPCICount')
    GROUP BY 
        TimeZone, Longitude, Latitude, ElementID, histType, dimValue
) AS ranked 
WHERE rank = 1;