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.
Sorting the view based on frequency in SQL Server
Just like in sub queries, you can't use ORDER BY
in a view definition in sql server unless you also use TOP
.
The reason for this is that Views are acted upon as if they where tables, and tables in sql server (in fact, in any relational database) are considered as not ordered sets.
Just like there is no meaning to the order of records stored in a table,
there is also no meaning to the order of records fetched by a view.
You can use a dirty hack and write SELECT TOP 100 PERCENT ...
and then use ORDER BY
, but I doubt if it has any meaning at all.
Having said all that, you can of course use ORDER BY
in any query that selects from a view.
ClientID
FROM dbo.tbm_cti_CustomTableItem_BudgetPeriod
GROUP BY StartDate,
ClientID
ORDER BY ClientID DESC,
StartDate ASC)
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?
回答:
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
blogs.msdn.com/b/queryoptteam/archive/2006/03/24/560396.aspx as explained here with TOP 100 Percent there is no order as it is guaranteed to be returning all rows hovever when SQL Server is evaluating top 99 it needs to perform order to make sure it returns the correct rows.
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.
调整之后,sql执行不报错,但是实际上没有排序效果
SELECT * FROM ( SELECT TOP (100) PERCENT StartDate, ClientID FROM dbo.tbm_cti_CustomTableItem_BudgetPeriod GROUP BY StartDate, ClientID ORDER BY ClientID DESC, StartDate ASC ) AS a;
https://stackoverflow.com/a/1622964/13338936
TOP (100) PERCENT is completely meaningless in recent versions of SQL Server, and it (along with the corresponding ORDER BY, in the case of a view definition or derived table) is ignored by the query processor.
You're correct that once upon a time, it could be used as a trick, but even then it wasn't reliable. Sadly, some of Microsoft's graphical tools put this meaningless clause in.
As for why this might appear in dynamic SQL, I have no idea. You're correct that there's no reason for it, and the result is the same without it (and again, in the case of a view definition or derived table, without both the TOP and ORDER BY clauses).