Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Error with Order By in Subquery

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?

like image 683
cagin Avatar asked Jun 12 '09 09:06

cagin


People also ask

Why ORDER BY does not work in subquery?

An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery. Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.

Why is ORDER BY in a from subquery ignored?

A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified.

Can subqueries contain GROUP BY and ORDER BY clause?

Subqueries cannot contain GROUP BY and ORDER BY clauses. Subqueries can contain ORDER BY but not the GROUP BY clause.

Can we use ORDER BY clause in subquery in Oracle?

Hi, Order by clause does not works inside a Sub-Query.No use of giving ORDER BY clause inside the sub query. Subquery gives values to the outer query and outer query only orders the value based on the order by clause.


2 Answers

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 
like image 164
Tomalak Avatar answered Sep 22 '22 23:09

Tomalak


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

like image 31
Patrick Wolf Avatar answered Sep 19 '22 23:09

Patrick Wolf