ORDER BY clause in SQL Server

Question

ORDER BY clause in SQL Server

up.sivakumar

SSC-Addicted

Points: 487
More actions
September 19, 2014 at 1:35 am

#296571

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

up.sivakumar (9/19/2014)
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

What happened to your system yesterday? You have a number of queries that worked one day and not the next. Something must have happened. Installed and update, upgraded to a new version. Queries don't just stop working suddenly when nothing else changes.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gulema.gerressu SSC Veteran Points: 208 More actions · Answer 2

Could it be that a column was deleted or modified. As the error suggests unless there was a e.region in the select statement or in the group by, I would be very surprised if it used to work.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

Quick thought, is it possible that you were connecting to a different server yesterday?

😎

gulema.gerressu SSC Veteran Points: 208 More actions · Answer 4

I can't see how connecting to the wrong server/database or any server setting or table change can be related to the error the query returns.

The query itself just has wrong syntax. If you use a "group by" you can't order by a column that is not in your select and not in your group by.