sql group by problem when sorting

I've got a problem with the error:

Column 'EXT_Design_Standard.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

开发者_运维百科I want to group records based on: cl.EXT_Design_Standard_ID

I have dynamically sorted the records based on (sort @directionOfSort variable)

Anyone know how to solve the below Problem?

DECLARE @fieldSort varchar(50) = 'Date Changed'
DECLARE @directionOfSort varchar(1) = 'D'

SELECT              min(cl.EXT_Design_Standard_ID)                                      AS [EXT_Design_Standard_ID],
                    ds.Name                                                         AS [Standards Name],
                    ds.Reference_Code                                               AS [Ref],
                    ( SELECT dbo.EXFN_StripHTML(ds.Description) )                   AS [Description],
                    (select replace(convert(varchar(20),cl.Change_On,106),' ','-')) + ' (' + CONVERT(VARCHAR(5) , cl.Change_On , 108)+ ')' AS [Date Changed],


FROM    EXT_Design_Standard_Change_Log cl      

INNER JOIN EXT_Design_Standard ds 
    on ds.EXT_Design_Standard_ID = cl.EXT_Design_Standard_ID      

INNER JOIN Contact_Summary cs
    on cs.Contact_ID = cl.Change_By

GROUP BY cl.EXT_Design_Standard_ID
ORDER BY 
    CASE WHEN @fieldSort ='Standards Name'
        THEN ROW_NUMBER() over (order by ds.Name) * 
            case when @directionOfSort = 'A' 
                THEN 1 ELSE -1 END
        END,
    CASE WHEN @fieldSort ='Ref'
        THEN ROW_NUMBER() over (order by ds.Reference_Code) * 
            case when @directionOfSort = 'A' 
                THEN 1 ELSE -1 END
        END,
    CASE WHEN @fieldSort ='Description'
        THEN ROW_NUMBER() over (order by ( SELECT dbo.EXFN_StripHTML(ds.Description) )) * 
            case when @directionOfSort = 'A' 
                THEN 1 ELSE -1 END
        END,
    CASE WHEN @fieldSort ='Date Changed'
        THEN ROW_NUMBER() over (order by cl.Change_On) * 
            case when @directionOfSort = 'A' 
                THEN 1 ELSE -1 END
        END

The select clause needs to match the group by fields. You can only use fields you have in the group by clause directly, every other is ambiguous and you need an aggregate function.

In your case, you have an aggregate function for the group by field, but not for others.

So this select clause

SELECT              min(cl.EXT_Design_Standard_ID),
                    ds.Name,
                    ds.Reference_Code

doesn't fit to that group by clause

GROUP BY cl.EXT_Design_Standard_ID

Assumed that your group by clause is what you actually want, you need to adapt the select clause to something like this:

SELECT              cl.EXT_Design_Standard_ID,
                    min(ds.Name),
                    min(ds.Reference_Code)