SQL Error with Order By in Subquery

sql-server sql-order-by
cagin picture cagin · Jun 12, 2009 · Viewed 191.7k times · Source

I'm working with SQL Server 2005.

My query is:

SELECT (
  SELECT COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4
  GROUP BY refKlinik_id
  ORDER BY refKlinik_id
) as dorduncuay

And the error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

How can I use ORDER BY in a sub query?

Answer

Tomalak picture Tomalak · Jun 12, 2009

This is the error you get (emphasis mine):

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

So, how can you avoid the error? By specifying TOP, would be one possibility, I guess.

SELECT (
  SELECT TOP 100 PERCENT
  COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4
  GROUP BY refKlinik_id
  ORDER BY refKlinik_id
) as dorduncuay

Related questions

How to update and order by using ms sql

Ideally I want to do this: UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC; In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher …

Read More
Combining ORDER BY AND UNION in SQL Server

How can I get first record of a table and last record of a table in one result-set? This Query fails SELECT TOP 1 Id,Name FROM Locations ORDER BY Id UNION ALL SELECT TOP 1 Id,Name FROM Locations ORDER BY …

Read More
Preserving ORDER BY in SELECT INTO

I have a T-SQL query that takes data from one table and copies it into a new table but only rows meeting a certain condition: SELECT VibeFGEvents.* INTO VibeFGEventsAfterStudyStart FROM VibeFGEvents LEFT OUTER JOIN VibeFGEventsStudyStart ON CHARINDEX(REPLACE(REPLACE(REPLACE(…

Read More