How to Create a SQL VIEW with ORDER BY Clause

Just as rows in a base table lack any defined ordering, rows available through a view do not appear with any default sorting. A view is a relational table, and the relational model defines a table as a set of rows. Since sets are not ordered - by definition - the rows in a view are not ordered, either. Therefore, an ORDER BY clause in the view definition is meaningless. The SQL standard (SQL:2003) does not allow an ORDER BY clause in a subselect in a CREATE VIEW statement, just as it is not allowed in a CREATE TABLE statement. However, sorted data can be obtained from a view, in the same way as any other table - as part of a query statement. Nevertheless, some DBMS (such as Oracle and SQL Server) allow a view to be created with an ORDER BY clause in a subquery, affecting how data is displayed.

Lets say you try to create a View using Order By Clause as show in the example below

CREATE VIEW SCHEMA_NAME/VIEW_NAME as (                              
    SELECT  PRODUCTS.COMPANY, PRODUCTS.ITEMNO, PRODUCTS.DESC1, 
        PRODUCTS.DESC2, PRODUCTS.PRICE, PRODUCTS.WEIGHT,       
        PRODUCTS.CATEGORY, CTGTABLE.CATNAME,                   
        PRODUCTS.MFG, MFGTABLE.MFGNAME                         
    FROM PRODUCTS left outer join CTGTABLE on                  
        PRODUCTS.COMPANY  = CTGTABLE.COMPANY  and              
        PRODUCTS.CATEGORY = CTGTABLE.CATEGORY                  
    left outer join MFGTABLE on                                
        PRODUCTS.COMPANY  = MFGTABLE.COMPANY  and              
        PRODUCTS.MFG      = MFGTABLE.MFG                       
    ORDER by PRODUCTS.COMPANY, PRODUCTS.ITEMNO                 
)                                                              

You will receive the following error message
The keyword ORDER was not expected here. A syntax error was detected at keyword ORDER. The partial list of valid tokens is ) UNION EXCEPT. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point.

The solution create subquery, here is how 

CREATE VIEW SCHEMA_NAME/VIEW_NAME as
SELECT * FROM
(SELECT * FROM TABLE_NAME ORDER BY 1) AS t 

Lets modify the above create view SQL statement to make it work

CREATE VIEW SCHEMA_NAME/VIEW_NAME as                               
Select * from (                                               
    SELECT  PRODUCTS.COMPANY, PRODUCTS.ITEMNO, PRODUCTS.DESC1,
        PRODUCTS.DESC2, PRODUCTS.PRICE, PRODUCTS.WEIGHT,      
        PRODUCTS.CATEGORY, CTGTABLE.CATNAME,                  
        PRODUCTS.MFG, MFGTABLE.MFGNAME                        
    FROM PRODUCTS left outer join CTGTABLE on                 
        PRODUCTS.COMPANY  = CTGTABLE.COMPANY  and             
        PRODUCTS.CATEGORY = CTGTABLE.CATEGORY                 
    left outer join MFGTABLE on                               
        PRODUCTS.COMPANY  = MFGTABLE.COMPANY  and             
        PRODUCTS.MFG      = MFGTABLE.MFG                      
       ORDER by PRODUCTS.COMPANY, PRODUCTS.ITEMNO) as t