SQL Error with Order By in Subquery

Solution 1:

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

Solution 2:

Besides the fact that order by doesn't seem to make sense in your query.... To use order by in a sub select you will need to use TOP 2147483647.

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

My understanding is that "TOP 100 PERCENT" doesn't gurantee ordering anymore starting with SQL 2005:

In SQL Server 2005, the ORDER BY clause in a view definition is used only to determine the rows that are returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

See SQL Server 2005 breaking changes

Hope this helps, Patrick

Solution 3:

If you're working with SQL Server 2012 or later, this is now easy to fix. Add an offset 0 rows:

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

Solution 4:

If building a temp table, move the ORDER BY clause from inside the temp table code block to the outside.

Not allowed:

SELECT * FROM (
SELECT A FROM Y
ORDER BY Y.A
) X;

Allowed:

SELECT * FROM (
SELECT A FROM Y
) X
ORDER BY X.A;