SQL Error with Order By in Subquery

Question

Hi all. i m working 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 1

You don't need order by in your sub query. Move it out into the main query, and include the column you want to order by in the subquery.

however, your query is just returning a count, so I don't see the point of the order by.

Answer 2

In this example ordering adds no information - the COUNT of a set is the same whatever order it is in!

If you were selecting something that did depend on order, you would need to do one of the things the error message tells you - use TOP or FOR XML

Answer 3

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

Answer 4

A subquery (nested view) as you have it returns a dataset that you can then order in your calling query. Ordering the subquery itself will make no (reliable) difference to the order of the results in your calling query.

As for your SQL itself: a) I seen no reason for an order by as you are returning a single value. b) I see no reason for the sub query anyway as you are only returning a single value.

I'm guessing there is a lot more information here that you might want to tell us in order to fix the problem you have.

Answer 5

Add the Top command to your sub query...

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

:)

Answer 6

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 2005 breaking changes

Hope this helps, Patrick

Answer 7

Thanks for the simple answer Robin Day. I was wondering why my subquery needed a top. Getting rid of the order by took care of it.