Column is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause

Question

Ok here's my View (vw_LiftEquip)

SELECT  dbo.tbl_equip_swl_unit.unit_id,
        dbo.tbl_equip_swl_unit.unit_name,
        dbo.tbl_equip_swl_unit.archived,
        dbo.tbl_categories.category_id,
        dbo.tbl_categories.categoryName,
        dbo.tbl_categories.parentCategory,
        dbo.tbl_categories.sub_category,
        dbo.tbl_categories.desc_category,
        dbo.tbl_categories.description,
        dbo.tbl_categories.miscellaneous,
        dbo.tbl_categories.category_archived,
        dbo.tbl_equip_swl_unit.unit_name AS Expr1,
        dbo.tbl_categories.categoryName AS Expr2,
        dbo.tbl_categories.description AS Expr3,
        dbo.tbl_equip_depts.dept_name,
        dbo.tbl_equip_man.man_name,
        dbo.tbl_Lifting_Gear.e_defects AS Expr7,
        dbo.tbl_Lifting_Gear.e_defects_desc AS Expr8,
        dbo.tbl_Lifting_Gear.e_defects_date AS Expr9,
        dbo.tbl_equipment.equipment_id,
        dbo.tbl_equipment.e_contract_no,
        dbo.tbl_equipment.slID,
        dbo.tbl_equipment.e_entered_by,
        dbo.tbl_equipment.e_serial,
        dbo.tbl_equipment.e_model,
        dbo.tbl_equipment.e_description,
        dbo.tbl_equipment.e_location_id,
        dbo.tbl_equipment.e_owner_id,
        dbo.tbl_equipment.e_department_id,
        dbo.tbl_equipment.e_manafacture_id,
        dbo.tbl_equipment.e_manDate1,
        dbo.tbl_equipment.e_manDate2,
        dbo.tbl_equipment.e_manDate3,
        dbo.tbl_equipment.e_dimensions,
        dbo.tbl_equipment.e_test_no,
        dbo.tbl_equipment.e_firstDate1,
        dbo.tbl_equipment.e_firstDate2,
        dbo.tbl_equipment.e_firstDate3,
        dbo.tbl_equipment.e_prevDate1,
        dbo.tbl_equipment.e_prevDate2,
        dbo.tbl_equipment.e_prevDate3,
        dbo.tbl_equipment.e_insp_frequency,
        dbo.tbl_equipment.e_swl,
        dbo.tbl_equipment.e_swl_unit_id,
        dbo.tbl_equipment.e_swl_notes,
        dbo.tbl_equipment.e_cat_id,
        dbo.tbl_equipment.e_sub_id,
        dbo.tbl_equipment.e_parent_id,
        dbo.tbl_equipment.e_last_inspector,
        dbo.tbl_equipment.e_last_company,
        dbo.tbl_equipment.e_deleted AS Expr11,
        dbo.tbl_equipment.e_deleted_desc AS Expr12,
        dbo.tbl_equipment.e_deleted_date AS Expr13,
        dbo.tbl_equipment.e_deleted_insp AS Expr14,
        dbo.tbl_Lifting_Gear.e_defects_action AS Expr15,
        dbo.tbl_equipment.e_rig_location,
        dbo.tbl_Lifting_Gear.e_add_type AS Expr17,
        dbo.tbl_Lifting_Gear.con_id,
        dbo.tbl_Lifting_Gear.lifting_date,
        dbo.tbl_Lifting_Gear.lifting_ref_no,
        dbo.tbl_Lifting_Gear.e_id,
        dbo.tbl_Lifting_Gear.inspector_id,
        dbo.tbl_Lifting_Gear.lift_testCert,
        dbo.tbl_Lifting_Gear.lift_rig_location,
        dbo.tbl_Lifting_Gear.inspected,
        dbo.tbl_Lifting_Gear.lifting_through,
        dbo.tbl_Lifting_Gear.liftingNDT,
        dbo.tbl_Lifting_Gear.liftingTest,
        dbo.tbl_Lifting_Gear.e_defects,
        dbo.tbl_Lifting_Gear.e_defects_desc,
        dbo.tbl_Lifting_Gear.e_defects_date,
        dbo.tbl_Lifting_Gear.e_defects_action,
        dbo.tbl_Lifting_Gear.lift_department_id,
        dbo.tbl_Lifting_Gear.lifting_loc
FROM    dbo.tbl_equipment
        INNER JOIN dbo.tbl_equip_swl_unit
                ON dbo.tbl_equipment.e_swl_unit_id = dbo.tbl_equip_swl_unit.unit_id
        INNER JOIN dbo.tbl_categories
                ON dbo.tbl_equipment.e_cat_id = dbo.tbl_categories.category_id
        INNER JOIN dbo.tbl_equip_depts
                ON dbo.tbl_equipment.e_department_id = dbo.tbl_equip_depts.dept_id
        INNER JOIN dbo.tbl_equip_man
                ON dbo.tbl_equipment.e_manafacture_id = dbo.tbl_equip_man.man_id
        INNER JOIN dbo.vwSubCategory
                ON dbo.tbl_equipment.e_sub_id = dbo.vwSubCategory.category_id
        INNER JOIN dbo.vwDescCategory
                ON dbo.tbl_equipment.e_cat_id = dbo.vwDescCategory.category_id
        INNER JOIN dbo.tbl_Lifting_Gear
                ON dbo.tbl_equipment.equipment_id = dbo.tbl_Lifting_Gear.e_id

And here's the select statement with subquery that I am using:

SELECT  *
FROM    vw_LiftEquip
WHERE   lifting_loc = ? AND 
        con_id = ? AND 
        EXPR11 = 
        'N'(
                SELECT MAX(lifting_date) AS maxLift
                FROM vw_LiftEquip
                WHERE e_id = equipment_id
            )
ORDER BY    lifting_ref_no,
            category_id,
            e_swl,
            e_serial

I get the error : Column "vw_LiftEquip.category_id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Can't see why its returning that error, this is admittedly the first time I've ran a subquery on such a complex view, and I am a bit lost, thanks in advance for any help. I have looked through the similar posts and can find no answers to this one, sorry if I am just being dumb.

Answer 1

You are missing AND between EXPR11 = 'N' and (SELECT MAX(...

Otherwise, it looks OK. MAX without GROUP BY is allowed if you have no other columns in the SELECT

Update: @hvd also noted that you have nothing to compare to MAX(lifting_date). See comment

Update 2,

SELECT  *
FROM    vw_LiftEquip v1
        CROSS JOIN
        (
             SELECT MAX(lifting_date) AS maxLift
             FROM vw_LiftEquip
             WHERE e_id = equipment_id
        ) v2
WHERE   v1.lifting_loc = ? AND 
        v1.con_id = ? AND 
        v1.EXPR11 = 'N'
ORDER BY    v1.lifting_ref_no,
            v1.category_id,
            v1.e_swl,
            v1.e_serial