CODEWITHSUNDEEP
sql-servert-sqlsql-view
Atreyix
Atreyix

Reputation: 3

Trying to make a view with a sample database and cannot seem to get it

I am trying to create a view for products with English descriptions in ascending order. Also titling it LastName_vProductLinesUSA.

Here are some screenshots of the database:

  1. Database overview

  2. SalesLT.vProductandDescription

  3. SalesLT.Product

  4. SalesLT.ProductDescription

I have tried using the query:

CREATE VIEW LastName_vProductLinesUSA 
AS 
    SELECT 
        [ProductID], [Name], [ProductModel], [Description] 
    FROM 
        [SalesLT.vProductAndDescription] 
    ORDER BY
        [Description];

But when I try to execute the query I get this error:

Msg 1033, Level 15, State 1, Procedure LastName_vProductLinesUSA, Line 4 [Batch Start Line 0]
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.

Upvotes: 0

Views: 52

Answers (2)

Jonathan Larouche
Jonathan Larouche

Reputation: 992

If you need a ordrered view, you have to use Top 100 percent

CREATE VIEW LastName_vProductLinesUSA 
AS SELECT TOP 100 PERCENT [ ProductID ], [ Name ], [ ProductModel ], [ Description ] 
FROM [ SalesLT.vProductAndDescription ] 
ORDER BY [ Description ] ;

Upvotes: -1

Caius Jard
Caius Jard

Reputation: 74635

Remove the ORDER BY from the view:

CREATE VIEW LastName_vProductLinesUSA 
AS SELECT [ ProductID ], [ Name ], [ ProductModel ], [ Description ] 
FROM [ SalesLT.vProductAndDescription ] 
ORDER BY [ Description ] ; <--- remove this

Without it:

CREATE VIEW LastName_vProductLinesUSA 
AS SELECT [ ProductID ], [ Name ], [ ProductModel ], [ Description ] 
FROM [ SalesLT.vProductAndDescription ]

If you want order to the results, order when you select from the view:

SELECT * FROM LastName_vProductLinesUSA
ORDER BY [ Description ] ;

Side note, on style; square brackets are fairly ugly. If you're trying to make them prettier by surrounding them with spaces, consider removing the square brackets instead (and strive to not use reserved words for identifiers so that you can carry on avoiding using [ ])

Upvotes: 2

Related Questions