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.

 

Why use Select Top 100 Percent?

select top 100 percent * from vie_trn_47 order by createdon desc

 

 

下面sql中的筛选，是有问题的。subquery里面不可以有排序

SELECT * FROM (SELECT StartDate,
       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.

回答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

 

调整之后，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).

 

 

 

 

 

 

 

 

 

作者:Chuck Lu    GitHub