Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

Why can not we have ORDER BY clause in CREATE VIEW (SELECT) statement - SQL Server ?

Hi,

Can you please explain me:

Why can not we have ORDER BY clause in CREATE VIEW (SELECT) statement ?

Thanks
0
milani_lucie
Asked:
milani_lucie
2 Solutions
 
LIONKINGCommented:
You can use the ORDER BY clause when you query the view, you don't need the ORDER BY when you create it, because at that momento you're not retrieving the data from the server, just "creating" the view.
0
 
reb73Commented:
Because a view is just a definition and does not actually contain data as such..

Ordering is done when you query the results or store it (clustered indexes).

0
 
sammySeltzerCommented:
Try something like this:

Create View ViewName as SELECT *  FROM (SELECT fieldname1, fieldname2...
from yourTable ORDER BY fieldname) as t

View doesn't like explicit ORDEr BY clause
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
sammySeltzerCommented:
View doesn't like explicit ORDEr BY clause when creating it...sorry
0
 
8080_DiverCommented:
There are decisions you make and those you live with.  In this case, Microsoft gets to make the decision and you get to live with it. ;-)
0
 
Anthony PerkinsCommented:
>>In this case, Microsoft gets to make the decision and you get to live with it. ;-) <<
I have to remember that one.  That is great!

But we can only hope the author does not resort to using TOP 100 PERCENT or some other cheesy kludge or they will be very disappointed when they discover that the ORDER BY clause is ignored.

But the simple answer is that even if they could use ORDER BY, it is a very bad idea.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now