DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to perform ORDER BY in a view?

The T-SQL ORDER BY clause is not allowed in view definition unless it includes the TOP, FOR XML or ROW_NUMBER() expressions.

When an ORDER BY create view attempted, following is the error message:

/* Msg 1033, Level 15, State 1, Procedure ww, Line 3
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 following Microsoft SQL Server T-SQL example scripts illustrate view definition with ORDER BY and usage. Note that only the ORDER BY clause in the outer SELECT query guarantees the desired sorting.

-- MSSQL create view with order by - T-SQL row number over order by
-- SQL inner join - left outer join - left join
USE AdventureWorks2008;
GO
CREATE VIEW vProductDetail
AS
  SELECT ROW_NUMBER()
           OVER(ORDER BY P.[Name])  AS RowID,
         P.[Name]                   AS ProductName,
         PM.[Name]                  AS ProductModel,
         PC.[Name]                  AS ProductCategory,
         PS.[Name]                  AS ProductSubCategory,
         PD.[DESCRIPTION],
         P.ProductNumber,
         COALESCE(P.Color,'')       AS Color,
         COALESCE(P.[Size],'')      AS [Size],
         COALESCE(P.[Weight],0.0)   AS [Weight],
         P.StandardCost,
         COALESCE(P.Style,'')       AS  Style,
         COALESCE(P.Class,'')       AS Class,
         P.ListPrice
  FROM   Production.Product P
         INNER JOIN Production.ProductSubcategory PS
         INNER JOIN Production.ProductCategory PC
           ON PS.ProductCategoryID = PC.ProductCategoryID
           ON P.ProductSubcategoryID = PS.ProductSubcategoryID
         INNER JOIN Production.ProductProductPhoto PPP
           ON P.ProductID = PPP.ProductID
         INNER JOIN Production.ProductPhoto PP
           ON PPP.ProductPhotoID = PP.ProductPhotoID
         LEFT OUTER JOIN Production.ProductDescription PD
         INNER JOIN Production.ProductModel PM
         INNER JOIN Production.ProductModelProductDescriptionCulture PCULT
           ON PM.ProductModelID = PCULT.ProductModelID
           ON PD.ProductDescriptionID = PCULT.ProductDescriptionID
           ON P.ProductModelID = PM.ProductModelID
  WHERE  (PCULT.CultureID = 'en')
GO
 
-- SQL view select results may be unordered despite the ordering in the view
SELECT * FROM   vProductDetail
GO
 
-- SQL view select results are ordered
SELECT * FROM   vProductDetail
ORDER BY RowID
GO
 
/* Partial results
 

RowID ProductName ProductModel ProductCategory ProductSubCategory
1 All-Purpose Bike Stand All-Purpose Bike Stand Accessories Bike Stands
2 AWC Logo Cap Cycling Cap Clothing Caps
3 Bike Wash - Dissolver Bike Wash Accessories Cleaners
4 Cable Lock Cable Lock Accessories Locks
5 Chain Chain Components Chains
6 Classic Vest, L Classic Vest Clothing Vests
7 Classic Vest, M Classic Vest Clothing Vests
8 Classic Vest, S Classic Vest Clothing Vests
9 Fender Set - Mountain Fender Set - Mountain Accessories Fenders
10 Front Brakes Front Brakes Components Brakes
11 Front Derailleur Front Derailleur Components Derailleurs
12 Full-Finger Gloves, L Full-Finger Gloves Clothing Gloves

 

*/
 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE