Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Using ORDER BY to Define a View

By: FYIcenter.com

(Continued from previous topic...)

Can You Use ORDER BY When Defining a View?

Sometimes you want the data in a view to be sorted and try to use the ORDER BY clause in the SELECT statement to define the view.

But SQL Server will not allow you to use ORDER BY to define a view without the TOP clause. The tutorial exercise below shows you what error you will get when using ORDER BY in a CREATE VIEW statement:

USE FyiCenterData;
GO

CREATE VIEW fyi_links_top AS
   SELECT id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO
Msg 1033, Level 15, State 1, Procedure fyi_links_top, Line 4
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.

CREATE VIEW fyi_links_top AS
   SELECT TOP 100 id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO

SELECT TOP 3 * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 
6192   999943   p o qisvrakk hk od 

The view seems to be sorted correctly.

(Continued on next topic...)

  1. What Are Views?
  2. How To Create a View on an Existing Table?
  3. How To See Existing Views?
  4. How To Drop Existing Views from a Database?
  5. How To Get a List of Columns in a View using "sys.columns"?
  6. How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
  7. How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
  8. How To Generate CREATE VIEW Script on an Existing View?
  9. How To Get the Definition of a View Out of the SQL Server?
  10. Can You Create a View with Data from Multiple Tables?
  11. Can You Create a View using Data from Another View?
  12. What Happens If You Delete a Table That Is Used by a View?
  13. Can You Use ORDER BY When Defining a View?
  14. How To Modify the Underlying Query of an Existing View?
  15. Can You Insert Data into a View?
  16. Can You Update Data in a View?
  17. Can You Delete Data from a View?
  18. How To Assign New Column Names in a View?
  19. How Column Data Types Are Determined in a View?
  20. How To Bind a View to the Schema of the Underlying Tables?
  21. How To Create an Index on a View?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...