Teradata error 3504 when trying to execute MAX with Subquery

Question

Pretty sure it must have something to do with the MAX, but not sure how to fix the issue.... SELECT Failed. 3504: (-3504)Selected non-aggregate values must be part of the associated group. Any Feedback/Advice/Assistance Appreciated. Trying to gather the last date completed for work on various maintenance plans for a large subset of equipment (30k+). I'm having a difficult time find the right questions to search to find out on my own.

SELECT 
MRC,
EQUIPMENT,
DESCRIPTION,
CLASSCODE,
CRITICALITY,
STATUS,
PM,
PMDESCRIPTION,
NEXTDUE,
PMTYPE,
ASSIGNEDBY,
ASSIGNEDTO,
FREQUENCY,
UOM,
WOEQUIP,
WORKORDER,
WORKORDERPM,
CHECKS,
MAX(LastCompleted) AS CDATE,
WOSTATUS
FROM
(SELECT
SE.EquipmentSurfaceAssetBK AS "MRC",
SE.EquipmentCodeBK AS "EQUIPMENT",
SE.EquipmentDescription AS "DESCRIPTION",
SE.EquipmentClassBK AS "CLASSCODE",
SE.EquipmentCriticality AS "CRITICALITY",
SE.EquipmentCostCode AS "COSTCODE",
SE.EquipmentStatus AS "STATUS",
PE.EqPMCodeBK AS "PM",
PM.PMDescription AS "PMDESCRIPTION",
PE.EqPMDueDate AS "NEXTDUE",
PE.EqPMType AS "PMTYPE",
PE.EqPMDeactivatedDate AS "DDATE",
PE.EqPMAssignedBy AS "ASSIGNEDBY",
PE.EqPMAssignedToResourceBK AS "ASSIGNEDTO",
PE.EqPMFrequency AS "FREQUENCY",
PE.EqPMFrequencyUOM AS "UOM",
WO.WorkOrderEquipmentcodeBK AS "WOEQUIP",
WO.WorkOrderCodeBK AS "WORKORDER",
WO.WorkOrderPMCodeBK AS "WORKORDERPM",
CASE
    WHEN WO.WorkOrderPMCodeBK = PE.EqPMCodeBK THEN 'YES' ELSE 'NO'
END AS "CHECKS",
WO.WorkOrderStatus AS "WOSTATUS",
WO.WorkOrderCompletedDate AS "LastCompleted"
FROM IDW_PL_SURFACE.DIMSurfaceEquipment SE 
JOIN IDW_PL_SURFACE.DIMEquipmentPM PE ON SE.EquipmentCodeBK = PE.EqPMEquipmentCodeBK
JOIN IDW_PL_SURFACE.DIMSurfacePM PM ON PE.EqPMCodeBK = PM.PMCodeBK
JOIN IDW_PL_SURFACE.DIMWorkOrder WO ON SE.EquipmentCodeBK = WO.WorkOrderEquipmentcodeBK) AS ST
WHERE 
1=1
AND
STATUS IN ( 'I', 'IDLE')
AND
DDATE is Null
AND
PMTYPE IN (  'V', 'F')
AND "CHECKS" IN ( 'YES')
GROUP BY
EQUIPMENT

Answer 1

The non-aggregated columns in your "select" do not match with the columns listed in your "group by". You can either add everything in your select (except LastCompleted) to the group by, or remove a bunch of columns from your select:

select
  EQUIPMENT,
  MAX(LastCompleted) as CDATE
from
--your subquery here
where STATUS in ('I','IDLE')
  and DDATE is Null
  and PMTYPE in ('V','F')
  and "CHECKS" in ('YES')
group by
  EQUIPMENT

Answer 2

If you just want the row with the latest LastCompleted per EQUIPMENT apply RANK/ROW_NUMBER:

SELECT
    SE.EquipmentSurfaceAssetBK AS "MRC",
    SE.EquipmentCodeBK AS "EQUIPMENT",
    SE.EquipmentDescription AS "DESCRIPTION",
    SE.EquipmentClassBK AS "CLASSCODE",
    SE.EquipmentCriticality AS "CRITICALITY",
    SE.EquipmentCostCode AS "COSTCODE",
    SE.EquipmentStatus AS "STATUS",
    PE.EqPMCodeBK AS "PM",
    PM.PMDescription AS "PMDESCRIPTION",
    PE.EqPMDueDate AS "NEXTDUE",
    PE.EqPMType AS "PMTYPE",
    PE.EqPMDeactivatedDate AS "DDATE",
    PE.EqPMAssignedBy AS "ASSIGNEDBY",
    PE.EqPMAssignedToResourceBK AS "ASSIGNEDTO",
    PE.EqPMFrequency AS "FREQUENCY",
    PE.EqPMFrequencyUOM AS "UOM",
    WO.WorkOrderEquipmentcodeBK AS "WOEQUIP",
    WO.WorkOrderCodeBK AS "WORKORDER",
    WO.WorkOrderPMCodeBK AS "WORKORDERPM",
    CASE
        WHEN WO.WorkOrderPMCodeBK = PE.EqPMCodeBK THEN 'YES' ELSE 'NO'
    END AS "CHECKS",
    WO.WorkOrderStatus AS "WOSTATUS",
    WO.WorkOrderCompletedDate AS "LastCompleted"
FROM IDW_PL_SURFACE.DIMSurfaceEquipment SE 
JOIN IDW_PL_SURFACE.DIMEquipmentPM PE
  ON SE.EquipmentCodeBK = PE.EqPMEquipmentCodeBK
JOIN IDW_PL_SURFACE.DIMSurfacePM PM
  ON PE.EqPMCodeBK = PM.PMCodeBK
JOIN IDW_PL_SURFACE.DIMWorkOrder WO
  ON SE.EquipmentCodeBK = WO.WorkOrderEquipmentcodeBK
WHERE 1=1
  AND STATUS IN ( 'I', 'IDLE')
  AND DDATE is Null
  AND PMTYPE IN (  'V', 'F')
  AND "CHECKS" IN ( 'YES')

QUALIFY RANK() OVER (PARTITION BY EQUIPMENT ORDER BY LastCompleted) = 1