CODEWITHSUNDEEP
sqlsql-server
Imad Abu Hayyah
Imad Abu Hayyah

Reputation: 444

Keep ORDER BY clause is invalid in view

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'

Upvotes: 1

Views: 337

Answers (3)

Ogundiji Bolade
Ogundiji Bolade

Reputation: 1

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'

Upvotes: -1

Zohar Peled
Zohar Peled

Reputation: 82504

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).

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 1

Related Questions