You must Sign In to post a response.
  • Category: SQL Server

    SQL Error : emp.REGION" is invalid in the ORDER BY clause


    Are you looking for a way to resolve SQL Error : emp.REGION" is invalid in the ORDER BY clause ? then read this thread to know more about it



    select CONVERT(VARCHAR(10),LEFT( e.REGION ,10))AS reg ,count(JOINING_DATE) from emp e
    group by CONVERT(VARCHAR(10),LEFT( e.REGION ,10))
    order by e.REGION


    The above SQL Script ran successfully up to yesterday. But today its throws the below error message.

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

    Now i have modified the SQL script as follows

    select CONVERT(VARCHAR(10),LEFT( e.REGION ,10))AS reg ,count(JOINING_DATE) from emp e
    group by CONVERT(VARCHAR(10),LEFT( e.REGION ,10))
    order by reg

    I want to know how the 1st SQL script runs successfully up to yesterday. Please clarify me
  • #751810
    Hi,

    It won't work, why means If you want to return any column in order by clause then it's must and should be available in either group by clause or in aggregate function but in your case it won't like that, check that yesterday query may be somebody did changes that is the reason it's getting this error.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #751817
    Hai SivS,
    I don't know how it was running before as when you have alias name then always in order by you need to have the alias name and not the name which is inside the scripts.
    So if you are writing like:

    select CONVERT(VARCHAR(10),LEFT(e.REGION ,10))AS reg Order by e.REGION

    it should not work and instead,

    select CONVERT(VARCHAR(10),LEFT(e.REGION ,10))AS reg Order by reg

    It should work fine.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #751818
    I have checked yesterday's query. its shown as below

    select CONVERT(VARCHAR(10),LEFT( e.REGION ,10))AS reg ,count(JOINING_DATE) from emp e
    group by CONVERT(VARCHAR(10),LEFT( e.REGION ,10))
    order by e.REGION

    It ran successfully up to yesterday. But today its throws the error message.
    i want to know how it ran successfully on yesterday?

  • #751821
    It will never be the case as This is a syntax error, if the column is not part of aggregate methods the order by clause will noe be used.
    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM


  • Sign In to post your comments