Keep ORDER BY clause is invalid in view

the order by clause is invalid in views
how to use order by clause in view in sql server
sql order by alphabetical not working
how to avoid order by in sql
order by sql
can we use order by in view in oracle
sql order by not working
sql order by two columns at the same time

I have the following SQL Statement:

Select CountryCodeTwoChar
FROM (Select CountryCodeTwoChar From [Country] Order By CountryName ) TBL
Where CountryCodeTwoChar = N'PS'

But I have the following Error from SQL Server:

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.

I know it gave the error for the Statement:

Select CountryCodeTwoChar From [Country] Order By CountryName

But that is auto-generated by the system so I cannot change it!

How to re-write the statement keeping the statement there (Select CountryCodeTwoChar From [Country] Order By CountryName)?

{Edit}

I Tried the following, is that good?

declare @userData TABLE(
                    CountryCodeTwoChar varchar(30) NOT NULL
                   )
Insert into @userData
Select CountryCodeTwoChar From [Country] Order By CountryName

Select CountryCodeTwoChar FROM @userData Where CountryCodeTwoChar = N'PS'

How to re-write the statement keeping the statement there (Select CountryCodeTwoChar From [Country] Order By CountryName)?

This will not be possible. There is NO WAY you can use that exact statement in a derived table. If you can't change it, you're out of luck.

If you are able to write multiple statements for a solution, so long as you use the exact statement above, then you can use that statement to populate a table variable, and then select from the table variable with your WHERE clause.

Using an ORDER BY in a view, is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. This view will never be called without a where clause though (which in my case would almost return the entire database). It will always be with WHERE [EventType].ID = x. However as soon as I add the where clause, the ORDER BY clause is ignored! Without the where clause it is working as expected.

Yes. Your workaround is good. The Optimizer will simply ignore the use of order by in this case because tables in a relational database are unsorted by nature - which is exactly the reason why you are not allowed to specify order by in a derived table or a view without also specifying top or for xml (and I'm guessing in later versions for json or offset...fetch, but too busy/lazy to check right now).

CASE WHEN statement for ORDER BY clause, and will generate a TOP 100 PERCENT within the SELECT as well. Complicated calculations will be done one and only one way with a view (I have a horror story from 25+ years ago about computing legal age two different ways in the same application). Access to only certain columns is restricted by a view. Again, in order by clause on a view or a table makes no sense.

Try and add TOP 100 PERCENT to the sub-select, That should help.

Select CountryCodeTwoChar
FROM (Select TOP 100 PERCENT 
CountryCodeTwoChar From [Country] 
Order By CountryName ) TBL
Where CountryCodeTwoChar = N'PS'

ORDER BY Clause, 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. The above error itself explains how one can use ORDER BY in view. It suggests that if we use ORDER BY with TOP, we can surely use ORDER BY. The reason the Order By is allowed in Views with a TOP clause is to allow the view to return the correct “TOP” so many records as needed by the ORDER BY (but still not in any guaranteed order). As an example, if you have a data set that has 20 unique [Salesman ID] and [Total Order Dollars], and you want to see the top 5.

ORDER BY in a View is not allowed – or is it?, The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. Order By clause does not work in View. I agree with all of you who say that there is no need of using ORDER BY in the View. ORDER BY should be used outside the View and not in the View. This example is another reason why one should not use ORDER BY in Views. Here is the quick example for the same.

SQL SERVER – ORDER BY Does Not Work – Limitation of the , Using OFFSET and FETCH in a view does not change the The ORDER BY clause is not valid in views, inline functions, derived tables, and  By removing the two extra order by clauses I receive this 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. – Ben May 10 '11 at 6:19

Avoid ORDER BY in SQL Server views, The ORDER BY clause is invalid in views, inline functions, derived You can not use ORDER BY in the CTE /View/Derived table unless it's  The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions My question is, how do I convert the code given above into a View ? I am using the following software versions -

Comments
  • But that is auto-generated by the system so I cannot change it! What system is that?
  • There is no need for the subselect. Select CountryCodeTwoChar from COuntry where countrycodetwochar = N'PS' order by countryname
  • My problem I have to keep this statement as is (Select CountryCodeTwoChar From [Country] Order By CountryName)
  • Well you can't. We can't solve problems which have impossible to satisfy constraints. (Although we'll often try :-))
  • Why do you have to keep it? We need more context.
  • This is simply a hack to use an order by clause in a view - which does not work. The optimizer knows better and ignores it.